Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
....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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
"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 | |
|
|
![]() |
||||
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) |