SUMIFS Formula Problem

[Keith]

I am trying to sum a column of values only if they meet certain criteria from
other columns. Here's a truncated example of what I'm trying to do:

A B C D
1 CBD Freeway 2
2 Urban Arterial 4
3 CBD Freeway 3
4 CBD HOV Freeway 6
5 CBD HOV Freeway 2
6 CBD Arterial 1
7 Urban Freeway 3

So I need to come up with the SUM from Column D when column B is "CBD" and
column C is "Freeway" or "HOV Freeway".

The answer (correct sum) should be 13.

I used =SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV Freeway",C1:C7,"Freeway") and I
got returned an answer of 0.

If I simplify it and just use =SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV
Freeway"), the formula works and I am correcrly returned 8. Even though I
also need the "Freeway" values.

I also tried to use "AND" or "OR" for the "HOV Freeway" and "Freeway"
combination from C1:C7, and it was a "bad" formula or it also returned 0.

In the SUMIFS formula can you not use two different criteria from the same
range (in this case C1:C7)?

Please help me out if possible.

Thank you very much.

 

[Mike H]

Try,

=SUMPRODUCT((B1:B7="CBD")*(C1:C7="HOV Freeway")*(D17))


Mike

 

[Keith]

....that returns the sum of the values that are "CBD" and "HOV Freeway", but
my problem is that in addition I need to include the values that are "CBD"
and "Freeway". The formula you gave me returned a value (sum) of 8, but I'm
looking for it to return a value (sum)of 13.

ie, the forumula needs to return (sum) all values that are CBD and HOV
Freeway and CBD and Freeway.

 

[Jim Rech]

I used =SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV Freeway",C1:C7,"Freeway") and
The new SUMIFS function ANDs the conditions together. So you get 0 because
there are no rows where column C is "HOV Freeway" and "Freeway". Each
criteria column should be referenced just once it appears.

So you need 2 SUMIFSs:

=SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV
Freeway")+SUMIFS(D17,B1:B7,"CBD",C1:C7,"Freeway")


--
Jim
|I am trying to sum a column of values only if they meet certain criteria
from
| other columns. Here's a truncated example of what I'm trying to do:
|
| A B C D
| 1 CBD Freeway 2
| 2 Urban Arterial 4
| 3 CBD Freeway 3
| 4 CBD HOV Freeway 6
| 5 CBD HOV Freeway 2
| 6 CBD Arterial 1
| 7 Urban Freeway 3
|
| So I need to come up with the SUM from Column D when column B is "CBD" and
| column C is "Freeway" or "HOV Freeway".
|
| The answer (correct sum) should be 13.
|
| I used =SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV Freeway",C1:C7,"Freeway") and
I
| got returned an answer of 0.
|
| If I simplify it and just use =SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV
| Freeway"), the formula works and I am correcrly returned 8. Even though I
| also need the "Freeway" values.
|
| I also tried to use "AND" or "OR" for the "HOV Freeway" and "Freeway"
| combination from C1:C7, and it was a "bad" formula or it also returned 0.
|
| In the SUMIFS formula can you not use two different criteria from the same
| range (in this case C1:C7)?
|
| Please help me out if possible.
|
| Thank you very much.
|
|
|

 

[Keith]

Thank you very much. So simple after all, but hard to realize. Thanks!

 

[Harlan Grove]

The new SUMIFS function ANDs the conditions together. So you get
0 because there are no rows where column C is "HOV Freeway" and
"Freeway". Each criteria column should be referenced just once it
appears.

So you need 2 SUMIFSs:

=SUMIFS(D17,B1:B7,"CBD",C1:C7,"HOV Freeway")
+SUMIFS(D17,B1:B7,"CBD",C1:C7,"Freeway")

....

Or one SUMPRODUCT,

=SUMPRODUCT((B1:B7="CBD")*(C1:C7={"HOV Freeway","Freeway"})*D17)

with the added benefit that you can use this formula in .XLS files and
older versions of Excel. The addition of COUNTIFS, SUMIFS and
AVERAGEIFS really does prove no one from MSFT reads the newsgroups.