Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 262
Default 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)?

Please help me out if possible.

Thank you very much.



  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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)?

Please help me out if possible.

Thank you very much.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 262
Default 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)?

Please help me out if possible.

Thank you very much.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,718
Default 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)?
|
| Please help me out if possible.
|
| Thank you very much.
|
|
|


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 262
Default 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)?
|
| Please help me out if possible.
|
| Thank you very much.
|
|
|





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 733
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 08:31 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"