Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct if
i need a "sumproductif" function:, a combination of sumif and sumproduct
e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#2
|
|||
|
|||
Hi
SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#3
|
|||
|
|||
can you give me an example? Say i have:
A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#5
|
|||
|
|||
=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10))
HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#6
|
|||
|
|||
=SUMPRODUCT(--(C1:C10="a"),A1:A10)+SUMPRODUCT(--(C1:C10="a"),B1:B10)
but SUMIF works as well in your example =SUMIF(C1:C10,"a",A1:A10)+SUMIF(C1:C10,"a",B1:B10) -- HTH Bob Phillips "sumproduct if" <sumproduct wrote in message ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#7
|
|||
|
|||
Hello Harald
In the example your formula will amount to 50 which means it adds values twice? Cordially Pascal "Harald Staff" a écrit dans le message de news: ... =SUMPRODUCT((A1:A4="BUY")*(B1:B4)*(C1:C4)) HTH. Best wishes Harald "sumproduct if" <sumproduct skrev i melding ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#8
|
|||
|
|||
.... or:
=SUMPRODUCT((A1:A11="BUY")*(B1:B11+C1:C11)) -- pozdrav! Berislav papou wrote: =SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10)) HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#9
|
|||
|
|||
"papou" skrev i melding
... Hello Harald In the example your formula will amount to 50 which means it adds values twice? I thought the OP wanted something like =SUMPRODUCT(B1:B4,C1:C4) with the criteria added, and that =SUMIF(A1:A4,"BUY",B1:B4)+SUMIF(A1:A4,"BUY",C1:C4) was the not the thing. Guess I was wrong. Best wishes Harald |
#10
|
|||
|
|||
"IF" the idea is to get 25 as the sum for all Buy's then this should do
it. =SUMPRODUCT((J2:J5="Buy")*K2:L5) -- Don Guillett SalesAid Software "sumproduct if" <sumproduct wrote in message ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#11
|
|||
|
|||
A little more concise:
=SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "papou" wrote in message ... =SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10)) HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#12
|
|||
|
|||
or even more so
=SUMPRODUCT((A1:A10="BUY")*(B1:C10)) -- HTH Bob Phillips "RagDyeR" wrote in message ... A little more concise: =SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "papou" wrote in message ... =SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10)) HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#13
|
|||
|
|||
I knew that when Don posted his at 5:30 this morning, and I didn't see it in
my O.E. for some reason. I kicked myself when I finally did see his. Are you repeating it here so that I kick myself again?<vbg I wish these MS servers would get it all together, so that I could at least put my foot in my mouth less often! -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Bob Phillips" wrote in message ... or even more so =SUMPRODUCT((A1:A10="BUY")*(B1:C10)) -- HTH Bob Phillips "RagDyeR" wrote in message ... A little more concise: =SUMPRODUCT((A1:A10="BUY")*((B1:B10)+(C1:C10))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "papou" wrote in message ... =SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A 1:A10="BUY")*(C1:C10)) HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... can you give me an example? Say i have: A B C BUY 2 4 BUY 4 8 SELL 3 4 BUY 5 2 if i want to sumproduct columns B and C only where column A = "BUY", what would be the formula? "papou" wrote: Hi SUMPRODUCT will allow multi-criteria whereas SUMIF only allows one criteria. Now if I understand rightly, you have one criteria and several columns to add. In which case you may add several SUMPRODUCT together. HTH Cordially Pascal "sumproduct if" <sumproduct a écrit dans le message de news: ... i need a "sumproductif" function:, a combination of sumif and sumproduct e.g. i need to sumproduct values in columns A and B if a condition in column C holds does anyone know of anything that would do this?? |
#14
|
|||
|
|||
"RagDyer" wrote in message ... I knew that when Don posted his at 5:30 this morning, and I didn't see it in my O.E. for some reason. I kicked myself when I finally did see his. Are you repeating it here so that I kick myself again?<vbg Not at all. I hadn't seen Don's reply when I sent this, I just sent it as a small ironic riposte as mine was even more long-winded (I'm English don't forget, we do lots of irony ;-)) I wish these MS servers would get it all together, so that I could at least put my foot in my mouth less often! Amen, then I would have seen Don's too. I still miss posts on this thing! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |