Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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 
Display Modes  


Similar Threads  
Thread  Forum  
SUMIFS() error?  Excel Worksheet Functions  
SumifS Multiple Sum Ranges  Excel Worksheet Functions  
SUMIFS error  Excel Discussion (Misc queries)  
SUMIFS with dates  Excel Worksheet Functions  
SumIfs  Excel Discussion (Misc queries) 