On 2002-10-24 18:56, planetpj wrote:
I am sure that Aladin will be familier with this question. I read an article that Harlan Grove did and I had a couple of theory questions about the syntax. Harlan mixed SUMPRODUCT with SUMIF. My approach to the example posted would be =SUMPRODUCT((B1:B5=E1)+(B1:B5=F1),C1:C5) but I have never used sumproduct with sumif before and this interests me. Can Somone please break this down for me (True & False) or in excel language 1 & 0 and show me how this formula work's
[...]
I like it & I'm not surprised that Harlan authored this...
The standard formula for multi-conditional summing with OR-conditions, using SUMPRODUCT, applied to the sample you provide, is
(1) =SUMPRODUCT((B1:B5=E1)+(B1:B5=F1),C1:C5)
I recently shortened this to...
(2) =SUMPRODUCT((B1:B5=E1:F1)*C1:C5)
Now we have Harlan's alternative:
(3) =SUMPRODUCT(SUMIF(B1:B5,E1:F1,C1:C5))
Formulas (1) and (2) can be reexpressed as...
(4) =SUMPRODUCT(SUMIF(B1:B5,{"PJ","Paul"},C1:C5))
that is, using an array constant.
Harlan's formula works differetly or goes by a different route...
It can be reexpressed as:
(5) =SUMIF(B1:B5,E1,C1:C5)+SUMIF(B1:B5,F1,C1:C5)
giving
=6+22
which SUMPRODUCT gets as array constant {6,22} and sums to 28.
The interesting question is whether (5) could operate faster than (2) ? Worth testing.
Aladin