# sum a column of numbers with 1 condition

Microsoft Excel 2003 (full product)
January 7, 2010 at 10:16:52
Specs: Windows XP
 I need to pull the total sum of surveys per Supervisor (2 supv):Supervisor Employ Survey TotalSmtih John A. 3Black John B. 10Black John C. 2etc...I need to find out the total number of surveys per Supv. As the above shows, Black has 12 and Smith has 3. I've tried several variations of arrays and I can't seem to get it.Thanks,TC

See More: sum a column of numbers with 1 condition

#1
January 7, 2010 at 10:50:20
 Your data is a bit confusing, you state that Black has 12 and Smith has 3 but you have listed:Smtih John A. 3Black John B. 10Black John C. 2So should it not be that John B Black has 10 and John C Black has 2?Assuming that the names are in column A and totals are in column BYou could use a =SUMIF formulaIn Column C enter the name of all your personnel one per line, then in column D enter the formula: =SUMIF(\$A\$1:\$A\$50,C1,\$B\$1:\$B\$50)Then drag the formula down.I’ve allowed for 50 rows of data, modify to suit your needs.MIKEhttp://www.skeptic.com/

Report •

#2
January 7, 2010 at 11:45:12
 He said "Survey"Maybe John Black got 10 ratings of B and 2 ratings of C for a total of 12 ratings, or 12 survey results.Just guessing...

Report •

#3
January 7, 2010 at 12:21:48
 Hi,This is my take on it:If this is your data, and the Supervisor names are in cells A6 and A7,``` A B C 1 Super Employ Surveys 2 Smith John A. 3 3 Black John B. 10 4 Black John C. 2 5 6 Smith 3 7 Black 12 ```Use this formula in B6, =SUMIF(\$A\$2:\$A\$4,A6,\$C\$2:\$C\$4)then drag it down to B7.Regards

Report •

Related Solutions

#4
January 7, 2010 at 13:06:21
 Now, come on Humar. What are the odds that a company would have 2 out 3 supervisors named "Black" and all of their subordinates are named "John"?:-)

Report •

#5
January 7, 2010 at 15:40:29

Report •

#6
January 7, 2010 at 18:43:28
 What is it, the BlackSmith family?????MIKE

Report •

#7
January 8, 2010 at 04:23:15
 Mike,I think you hit the nail on the head - or perhaps made the nail !

Report •

#8
January 18, 2010 at 09:06:44
 Thanks for the responses, but I still can't get it to work. I failed to mention that I am calculating the data from another worksheet (tab). I'll try to explain again:SUMMARY BY TEAM Supv. # Team # + Surveys Mike K. 14 ? Jon C. 13 ? Joe M. 4 ? Grand Total ?? On the other worksheet, named Surveys, the data is as follows:Tech Name Supervisor # + SurveysRICHARD Jon C. 0CHARLES Mike K. 0JOSEPH Jon C. 5MORGAN Mike K. 0ANDREW Jon C. 0STARR,DAVID Jon C. 5MICHAEL Joe M. 3LARRY Joe M. 4JUSTIN Joe M. 2This continues down to row 99. The idea is to change this worksheet with the monthly data each month and summarize it on another worksheet, named Summary.=sum(Survey!\$C\$2:\$C\$99,etc... not sure what to add to get this summarized.Thanks!

Report •

#9
January 18, 2010 at 11:35:01
 Hi,I still find this confusing.Can you post your data using the
and
tags, you can enter a set of these tags using the icon at the top of the message box.This enables headings and data to line-up.In your most recent post you have:SUMMARY BY TEAMSupv. # Team # + SurveysMike K. 14 ? You start with Supv. # which usually means Supv. numberbut there is no number in the first column.Is Mike K. the supervisor's nameIs 14 the team numberWhat does + Surveys mean.What does the ? representIn the second part of your post are the names in UPPER CASE the names of the Techs.Is there any significance to STARR,DAVID, as it is different to the others.IF ... your table of data on the worksheet named "Surveys" is this:``` A B C 1 Tech name Supervisor Surveys 2 RICHARD Jon C. 0 3 CHARLES Mike K. 0 4 JOSEPH Jon C. 5 5 MORGAN Mike K. 0 6 ANDREW Jon C. 0 7 STARR,DAVID Jon C. 5 8 MICHAEL Joe M. 3 9 LARRY Joe M. 4 10 JUSTIN Joe M. 2 ```then IF ... you have this list of Supervisor names on a worksheet named "Summary"``` A 1 Supervisors 2 Mike K. 3 Jon C. 4 Joe M. ```enter this formula in cell B2`=SUMIF(Surveys!\$B\$2:\$B\$10,A2,Surveys!\$C\$2:\$C\$10)`Note the \$ signsNow drag this formula down to cell B4 where it will look like this:`=SUMIF(Surveys!\$B\$2:\$B\$10,A4,Surveys!\$C\$2:\$C\$10)`Enter this in cell B5:`=SUM(B2:B4)`You will get this:``` A B 1 Supervisors Surveys 2 Mike K. 0 3 Jon C. 10 4 Joe M. 9 5 Grand Total 19 ```Regards

Report •