Multiple sumifs?

[sholmb1]

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]

=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]

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]

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]

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]

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]

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.