Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumif with criteria list
I have a row of data that I want to sum if the corresponding collumns are one
of several departments. I can't figure out how to use multiple criteria with the sumif function and the data is not in typical DB format so I can't use the DSUM function. Does anyone know how to solve this problem? Thanks in advance, Brian Sample Company A Company B Company C Company D Assets 15 5 20 25 Question: What are the assets for companies A and B. My actual situation could have 15 different search requirements. |
#2
|
|||
|
|||
Hi Brian,
a couple of options: =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2)) =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2) The fixed arrays can be replaced by range references: in the 2nd formula the range must be vertical (or horizontal with TRANSPOSE function). Regard, KL "Brian Barbre" wrote in message ... I have a row of data that I want to sum if the corresponding collumns are one of several departments. I can't figure out how to use multiple criteria with the sumif function and the data is not in typical DB format so I can't use the DSUM function. Does anyone know how to solve this problem? Thanks in advance, Brian Sample Company A Company B Company C Company D Assets 15 5 20 25 Question: What are the assets for companies A and B. My actual situation could have 15 different search requirements. |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,{"Company A","Company B"},0))),B2:E2) OR =SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,$G$1:$G$2,0))),B2:E2) ....where G1:G2 contains your company names. Hope this helps! In article , Brian Barbre wrote: I have a row of data that I want to sum if the corresponding collumns are one of several departments. I can't figure out how to use multiple criteria with the sumif function and the data is not in typical DB format so I can't use the DSUM function. Does anyone know how to solve this problem? Thanks in advance, Brian Sample Company A Company B Company C Company D Assets 15 5 20 25 Question: What are the assets for companies A and B. My actual situation could have 15 different search requirements. |
#4
|
|||
|
|||
Thanks KL,
That solved my problem. I was actually able to just enclose the SUMIF formula I alread had with the SUMPRODUCT formula. Is the reason why the SUMPRODUCT formula works because it is able to handle arrays where as the SUMIF is not? "KL" wrote: Hi Brian, a couple of options: =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2)) =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2) The fixed arrays can be replaced by range references: in the 2nd formula the range must be vertical (or horizontal with TRANSPOSE function). Regard, KL "Brian Barbre" wrote in message ... I have a row of data that I want to sum if the corresponding collumns are one of several departments. I can't figure out how to use multiple criteria with the sumif function and the data is not in typical DB format so I can't use the DSUM function. Does anyone know how to solve this problem? Thanks in advance, Brian Sample Company A Company B Company C Company D Assets 15 5 20 25 Question: What are the assets for companies A and B. My actual situation could have 15 different search requirements. |
#5
|
|||
|
|||
Well, not exactly. SUMIF does return an array if the second argument is an
array. The question is how do you sum the members of that array. You can, of course, use the SUM function, but will have to confirm it by Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to handle arrays. KL "Brian Barbre" wrote in message ... Thanks KL, That solved my problem. I was actually able to just enclose the SUMIF formula I alread had with the SUMPRODUCT formula. Is the reason why the SUMPRODUCT formula works because it is able to handle arrays where as the SUMIF is not? "KL" wrote: Hi Brian, a couple of options: =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2)) =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2) The fixed arrays can be replaced by range references: in the 2nd formula the range must be vertical (or horizontal with TRANSPOSE function). Regard, KL "Brian Barbre" wrote in message ... I have a row of data that I want to sum if the corresponding collumns are one of several departments. I can't figure out how to use multiple criteria with the sumif function and the data is not in typical DB format so I can't use the DSUM function. Does anyone know how to solve this problem? Thanks in advance, Brian Sample Company A Company B Company C Company D Assets 15 5 20 25 Question: What are the assets for companies A and B. My actual situation could have 15 different search requirements. |
#6
|
|||
|
|||
Makes Sense...thanks for the help
Brian "KL" wrote: Well, not exactly. SUMIF does return an array if the second argument is an array. The question is how do you sum the members of that array. You can, of course, use the SUM function, but will have to confirm it by Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to handle arrays. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |