Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
I want to get the sum of all transactions in my table of type "RI" or "II" or
"AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
=SUMPRODUCT((<wh column=<wh)*(<type column={"RI",II","A"})*(<merch
column=<merch)*(<amt column)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
Example
=SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
Thank you. This works for me.
Follow up question. Is the "0" necessary? The double unary parses the array as a boolean, so any non zero value gets evaluated as TRUE (1), correct? "Peo Sjoblom" wrote: Example =SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
No, it is not necessary here. It is not the double unary here that does the
coercing, but the +, TRUE+FALSE+FALSE=1, FALSE+FALSE+TRUE=1, etc., and FALSE+FALSE+FALSE=0. In fact this double unary is superfluous, all you need is =SUMPRODUCT(--(wh_column=I1),((type_column=J1)+(type_column=K1) +(type_column=L1)),amt_column) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Marcotte A" wrote in message ... Thank you. This works for me. Follow up question. Is the "0" necessary? The double unary parses the array as a boolean, so any non zero value gets evaluated as TRUE (1), correct? "Peo Sjoblom" wrote: Example =SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
Just to add, what I described was because of that particular test, where
only one column was being tested for 3 different values, that is only one could be true.If you were doing an OR on separate columns, where more than one condition could be met, the 0 test is useful so as to avoid double counting =SUMPRODUCT(--(wh_column=I1),--(((type_column=J1)+(type2_column=K1)+(type_column= L1))0),amt_column) and you will notice you need the double unary again, because the 0 3 item test will resolve to TRUE or FALSE. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Marcotte A" wrote in message ... Thank you. This works for me. Follow up question. Is the "0" necessary? The double unary parses the array as a boolean, so any non zero value gets evaluated as TRUE (1), correct? "Peo Sjoblom" wrote: Example =SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
As a further aside, you can also handle the separate column ORs using SIGN
=SUMPRODUCT(--(wh_column=I1),SIGN((type_column=J1)+(type2_colum n=K1)+(type_column=L1)),amt_column) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Marcotte A" wrote in message ... Thank you. This works for me. Follow up question. Is the "0" necessary? The double unary parses the array as a boolean, so any non zero value gets evaluated as TRUE (1), correct? "Peo Sjoblom" wrote: Example =SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - multiple conditions
Itis not needed this time since you are looking in the same range so easiest
is to use an array hardcoded. However I use it for consistency, if indeed you were using more than one range in your OR using the same criteria it is needed x x b x c x x x x x c x a x x x c x a x x x the above is in A2:B10, assume we want to count x using OR, basically saying =OR(A2="x",B2="x") then copied down and counted would give 11, if you use (A2:A10="x")+(B2:B10="x") it will return 16 whereas --((A2:A12="x")+(B2:B12="x")0) will return 11 so to me I know that if I use this it will work in all cases -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... Thank you. This works for me. Follow up question. Is the "0" necessary? The double unary parses the array as a boolean, so any non zero value gets evaluated as TRUE (1), correct? "Peo Sjoblom" wrote: Example =SUMPRODUCT(--(wh_column=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1 )0),amt_column) where the type_column can be either J1, K1 or L1 and where wh_column is greater than or equal to I1 then sum those values in amt_column -- Regards, Peo Sjoblom "Marcotte A" wrote in message ... I want to get the sum of all transactions in my table of type "RI" or "II" or "AI". I am also filtering for other conditions (warehouse and merchandise class), but these conditions have only one option for each cell in my spreadsheet. In other words, for each warehouse and merchandise class, I need to show the sum total of those three types of transactions. I could do something like this: =SUMPRODUCT(--(<wh column=<wh),--(<type column="RI"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="II"),--(<merch column=<merch),'<amt column)+SUMPRODUCT(--(<wh column=<wh),--(<type column="AI"),--(<merch column=<merch),'<amt column) where everything in "<" is a cell or array reference. That is really long and messy. Is there a way to put an OR statement inside the SUMPRODUCT? Something along the lines of changing "--(<type column="RI")" to "--(OR(<type column="RI",<type column="II",<type column="AI"))" which doesn't work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct, multiple conditions.. but I also want to "minus" somet | Excel Worksheet Functions | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
Sumproduct Multiple < Conditions | Excel Worksheet Functions | |||
Multiple SumProduct conditions | Excel Worksheet Functions | |||
Sumproduct Multiple Conditions | Excel Worksheet Functions |