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

"Mike H" wrote:

Try,

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


Mike

"Keith" wrote:

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(D1:D7,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(D1:D7,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.