SumProductIf?

[BarrySNYC14]

I would like to be able to do a “sumproductif” Specifically, for every
element in column A that matches a criterion, multiply the corresponding
elements in columns b and c, and add up those products. (I don’t want
to just multiply elements in B and C, throw the result in column D, then
do a sumif using columns A and D) Thanks in advance for any help you
can provide.

 

[JE McGimpsey]

One way:

Assume your criterion is that the value in column A is greater than 10:


=SUMPRODUCT(--(A1:A1000>10), B1:B1000, C1:C1000)


The double unary minuses convert the Boolean (TRUE/FALSE) values
returned by the comparison term to numbers, which is what SUMPRODUCT
requires.

 

[BarrySNYC14]

So, to state the obvious, then, three numbers are multiplied, with
True=1, False=0 being the first. I was unaware that I could put a
conditional statement into SUMProduct. Is this documented somewhere?
Thanks for your help.

Barry

 

[JE McGimpsey]

you are correct. I've never seen this specifically documented in Help,
but it's a widely known technique.