General Sumproduct Question

[Aaron]

When using Sumproduct as a multiple criteria sumif function, I've seen it
used two ways:

sumproduct(()*()*())

sumproduct(--(),--(),())

My question is are these just two ways of doing the same thing, or does one
do something the other does not?

 

[Tom Hutchins]

Essentially, both ways are coercing the TRUE/FALSE result of each criteria to
be 1 (for TRUE) or 0 (for FALSE). Here is a link to a great tutorial on
SumProduct. It will answer your question in mucj more detail.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

 

[Tyro]

The -- takes a logical value of TRUE or FALSE and makes the value 1 or 0.
The first - coerces Excel to make the logical value for TRUE numeric,
i.e. -1. The second - makes the -1 positive - ie. 1. FALSE becomes 0 which
is neither negative or positive. Then SUMPRODUCT multiplies the resulting
values by the rest of the arguments. The format with the * will cause Excel
to make logical values numeric. TRUE becomes 1 and FALSE becomes 0. Then the
multiplication is done. The effect of the two formulas is the same. Some
people prefer one version over the other. SUMPRODUCT can also add, subtract,
divide and exponentiate.

Tyro

 

[Bob Phillips]

Yes, they certainly do.

Imagine this data

Id Date Amt1 Amt2 Amt3
X 01-Jan 19 13 19
Y 03-Feb 18 20 20
X 01-Feb 18 20 18


Try the two formula and see what happens

=SUMPRODUCT(--(A1:A4="X"),--(C1:C4>10),C1:C4)

and

=SUMPRODUCT((A1:A4="X")*(C1:C4>10)*(C1:C4))

Then conversely, try these two

=SUMPRODUCT(--(A2:A4="X"),--(MONTH(B2:B4)=1),C2:E4)

and

=SUMPRODUCT((A2:A4="X")*(MONTH(B2:B4)=1)*(C2:E4))

Horses for courses.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)