sumproduct if

[Guest]

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??

 

[papou]

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

 

[Guest]

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?

 

[Harald Staff]

=SUMPRODUCT((A1:A4="BUY")*(B1:B4)*(C1:C4))

HTH. Best wishes Harald

 

[papou]

=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

 

[Bob Phillips]

=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)

 

[papou]

Hello Harald
In the example your formula will amount to 50 which means it adds values
twice?

Cordially
Pascal

 

[b&s]

.... or:
=SUMPRODUCT((A1:A11="BUY")*(B1:B11+C1:C11))

 

[Harald Staff]

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

 

[Don Guillett]

"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)

 

[RagDyeR]

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 !
--------------------------------------------------------------------


=SUMPRODUCT((A1:A10="BUY")*(B1:B10))+SUMPRODUCT((A1:A10="BUY")*(C1:C10))

HTH
Cordially
Pascal

 

[Bob Phillips]

or even more so

=SUMPRODUCT((A1:A10="BUY")*(B1:C10))

 

[RagDyer]

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!

 

[Bob Phillips]

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!