Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 262
SUMIFS Formula Problem

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)?

Thank you very much.

#2
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
SUMIFS Formula Problem

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)?

Thank you very much.

#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 262
SUMIFS Formula Problem

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

Thank you very much.

#4
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 2,718
SUMIFS Formula Problem

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

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(D1:D7,B1:B7,"CBD",C1:C7,"HOV
Freeway")+SUMIFS(D1:D7,B1:B7,"CBD",C1:C7,"Freeway" )

--
Jim
"Keith" wrote in message
...
|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)?
|
|
| Thank you very much.
|
|
|

#5
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 262
SUMIFS Formula Problem

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

"Jim Rech" wrote:

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

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(D1:D7,B1:B7,"CBD",C1:C7,"HOV
Freeway")+SUMIFS(D1:D7,B1:B7,"CBD",C1:C7,"Freeway" )

--
Jim
"Keith" wrote in message
...
|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)?
|
|
| Thank you very much.
|
|
|

#6
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 733
SUMIFS Formula Problem

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

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(D1:D7,B1:B7,"CBD",C1:C7,"HOV Freeway")
+SUMIFS(D1:D7,B1:B7,"CBD",C1:C7,"Freeway")

....

Or one SUMPRODUCT,

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

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post fgrose Excel Worksheet Functions 6 October 29th 09 06:05 PM Harlan Grove[_2_] Excel Worksheet Functions 0 May 23rd 07 08:40 PM NicoleC Excel Discussion (Misc queries) 8 May 7th 07 05:19 AM Chinni Krishna Reddy[_2_] Excel Worksheet Functions 2 April 16th 07 10:02 PM timson Excel Discussion (Misc queries) 3 January 26th 07 08:46 PM

All times are GMT +1. The time now is 07:22 AM.

The comments are property of their posters.