Multiple sumifs?

sholmb1 · Apr 10th, 2002, 02:16 AM

I need to get a total of all values in column C only if column A equals Department and Column B equal TeamA.

A________________B_______________C
Department______TeamA___________.25
Department______TeamA__________1.0
Division________TeamA__________2.0
Department______TeamB__________3.0
Department______TeamA___________.25

I only want the total to include rows 1,2 and 5. The value would be 1.5

I tried the SUMIF command, but that only includes one column of criteria. (That I know how to do).

Is there an easy way to do this with a formula?

Thanks.

[ This Message was edited by: sholmb1 on 2002-04-09 21:17 ]

[ This Message was edited by: sholmb1 on 2002-04-09 21:17 ]

[ This Message was edited by: sholmb1 on 2002-04-09 21:20 ]

Dave Patton · Apr 10th, 2002, 02:22 AM

=SUMPRODUCT((A2:A6="Department")*(B2:B6="TeamA")*(C2:C6))

or use Cell references for Criteria

=SUMPRODUCT((A2:A6=G1)*(B2:B6=G2)*(C2:C6))

Revise references as necessary.

or set up as Database and use DSum.

[ This Message was edited by: Dave Patton on 2002-04-09 21:24 ]

Yogi Anand · Apr 10th, 2002, 02:38 AM

Hi Sholmb1:
You could use the following formula:

=SUMIF(A2:A6,"=Department",C2:C6)-SUMIF(B2:B6,"=TeamB",C2:C6)

that results in 1.5

Yogi Anand · Apr 10th, 2002, 02:45 AM

Quote:

On 2002-04-09 21:22, Dave Patton wrote:

=SUMPRODUCT((A2:A6="Department")*(B2:B6="TeamA")*(C2:C6))

or use Cell references for Criteria

=SUMPRODUCT((A2:A6=G1)*(B2:B6=G2)*(C2:C6))

Revise references as necessary.

or set up as Database and use DSum.

[ This Message was edited by: Dave Patton on 2002-04-09 21:24 ]

Hi Dave:
I like this better than what I posted above ... this is more comprehensive in applicability. T H A N K S !

[ This Message was edited by: Yogi Anand on 2002-04-09 21:46 ]

sholmb1 · Apr 10th, 2002, 03:42 PM

Thanks Yogi and Dave.

I think the SUMIF forumla misses the point. If the value of 3 is changed in my example to say 34, The answer is not 1.5 The SUMIF solution would work if the values in column C were constant.

I am having some luck with the SUMPRODUCT formula. It works when I enter it on the same worksheet, but I am going between workbooks and getting a #value error when I add in the last columns (C) criteria into the formula (It works with only having column A and B in the sumproduct formula although it returns a total for the number of matches between the two columns (In my example above it would be 3). I don't know how, when I add in column C criteria that it changes to 1.5.)

If you have other suggestions, I am open to anything.

Thanks again for all your help.

Aladin Akyurek · Apr 10th, 2002, 03:52 PM

Quote:

On 2002-04-10 10:42, sholmb1 wrote:
Thanks Yogi and Dave.

I think the SUMIF forumla misses the point. If the value of 3 is changed in my example to say 34, The answer is not 1.5 The SUMIF solution would work if the values in column C were constant.

I am having some luck with the SUMPRODUCT formula. It works when I enter it on the same worksheet, but I am going between workbooks and getting a #value error when I add in the last columns (C) criteria into the formula (It works with only having column A and B in the sumproduct formula although it returns a total for the number of matches between the two columns (In my example above it would be 3). I don't know how, when I add in column C criteria that it changes to 1.5.)

If you have other suggestions, I am open to anything.

Thanks again for all your help.

I couldn't follow what you're saying about the SUMPRODUCT formula.

Changing values in the ranges where SUMPRODUCT looks at will not affect the formula at all.

What do you mean by moving between workbooks? Are you trying to apply the formula say from within WB1 on data in WB2?

sholmb1 · Apr 12th, 2002, 07:45 PM

I was able to get it to work. My issue was with 1) my formula. With the SUMPRODUCT function, all ranges need to be the same. (SUMPRODUCT(A4:A10)*(B4:B10)*(C4:C10)). 2) non numeric data in numeric fields. I had spaces in my column C that would give back a #value error. Once I cleaned all this up, it works like a charm. Thanks for all your help.

Apr 10th, 2002, 02:38 AM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,448	Hi Sholmb1: You could use the following formula: =SUMIF(A2:A6,"=Department",C2:C6)-SUMIF(B2:B6,"=TeamB",C2:C6) that results in 1.5 __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 02:16 AM	#1
sholmb1 Join Date: Apr 2002 Posts: 5	I need to get a total of all values in column C only if column A equals Department and Column B equal TeamA. A________________B_______________C Department______TeamA___________.25 Department______TeamA__________1.0 Division________TeamA__________2.0 Department______TeamB__________3.0 Department______TeamA___________.25 I only want the total to include rows 1,2 and 5. The value would be 1.5 I tried the SUMIF command, but that only includes one column of criteria. (That I know how to do). Is there an easy way to do this with a formula? Thanks. [ This Message was edited by: sholmb1 on 2002-04-09 21:17 ] [ This Message was edited by: sholmb1 on 2002-04-09 21:17 ] [ This Message was edited by: sholmb1 on 2002-04-09 21:20 ]

Apr 10th, 2002, 02:22 AM	#2
Dave Patton Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 1,761	=SUMPRODUCT((A2:A6="Department")(B2:B6="TeamA")(C2:C6)) or use Cell references for Criteria =SUMPRODUCT((A2:A6=G1)(B2:B6=G2)(C2:C6)) Revise references as necessary. or set up as Database and use DSum. [ This Message was edited by: Dave Patton on 2002-04-09 21:24 ]

Apr 10th, 2002, 03:42 PM	#5
sholmb1 Join Date: Apr 2002 Posts: 5	Thanks Yogi and Dave. I think the SUMIF forumla misses the point. If the value of 3 is changed in my example to say 34, The answer is not 1.5 The SUMIF solution would work if the values in column C were constant. I am having some luck with the SUMPRODUCT formula. It works when I enter it on the same worksheet, but I am going between workbooks and getting a #value error when I add in the last columns (C) criteria into the formula (It works with only having column A and B in the sumproduct formula although it returns a total for the number of matches between the two columns (In my example above it would be 3). I don't know how, when I add in column C criteria that it changes to 1.5.) If you have other suggestions, I am open to anything. Thanks again for all your help.

Apr 12th, 2002, 07:45 PM	#7
sholmb1 Join Date: Apr 2002 Posts: 5	I was able to get it to work. My issue was with 1) my formula. With the SUMPRODUCT function, all ranges need to be the same. (SUMPRODUCT(A4:A10)(B4:B10)(C4:C10)). 2) non numeric data in numeric fields. I had spaces in my column C that would give back a #value error. Once I cleaned all this up, it works like a charm. Thanks for all your help.