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 Total
Smtih John A. 3
Black John B. 10
Black John C. 2
etc...
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

Report •


#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. 3
Black John B. 10
Black John C. 2

So 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 B
You could use a =SUMIF formula

In 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.

MIKE

http://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
Hi DerbyDad03,

Obviously a Family firm.

:-)


Report •

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

MIKE

http://www.skeptic.com/


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 # + Surveys
RICHARD Jon C. 0
CHARLES Mike K. 0
JOSEPH Jon C. 5
MORGAN Mike K. 0
ANDREW Jon C. 0
STARR,DAVID Jon C. 5
MICHAEL Joe M. 3
LARRY Joe M. 4
JUSTIN Joe M. 2
This 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 <pre> and </pre> 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 TEAM

Supv. # Team # + Surveys
Mike K. 14 ?

You start with Supv. # which usually means Supv. number
but there is no number in the first column.

Is Mike K. the supervisor's name
Is 14 the team number
What does + Surveys mean.
What does the ? represent

In 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 $ signs

Now 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 •


Ask Question