SOMEPRODUCT SUMIF SYNTAX (THEORY QUESTION)

[planetpj]

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

Harlan grove Formula.xls
	B	C	D	E	F	G
1	PJ	2		PJ	Paul	28
2	Jim	3
3	PJ	4
4	Al	5
5	Paul	22
6
7			Harlan'sGrove'sFormula
8			28
9			28
Sheet1

 

[Aladin Akyurek]

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

 

[IML]

Highlighting terms and hitting f9 is a great way to see what it is doing. Your formula simplifies to
'=SUMPRODUCT(({TRUE;FALSE;TRUE;FALSE;FALSE})+({FALSE;FALSE;FALSE;FALSE;TRUE}),{2;3;4;5;22})
and then
'=SUMPRODUCT({1;0;1;0;1},{2;3;4;5;22})

A variation of your formula would be
=SUMPRODUCT((B1:B5=E1:F1)*C1:C5)
which first goes to
'=SUMPRODUCT(({TRUE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE})*{2;3;4;5;22})
and then
'=SUMPRODUCT({1;0;1;0;1},{2;3;4;5;22})

This voodoo magic is explained a few times by Aladin. Search for posts by him that include key word Scalar.

edit: You could also see what he typed in right above me, you know, if that is easier or possible more relevant in any way
This message was edited by IML on 2002-10-24 19:40

 

[planetpj]

Aladin & IML thank you both for the insite back on the subject. I agree with both of you it's great to see multiple approaches to different formulas. That's how we all grow. I respect you both and am thankful to have people to communicate with and learn continueously. God Bless and thank you both!!

 

[planetpj]

Here is the link to the actual post that I saw and I made a mistake the actual author of this was George Simms. Aladin made me aware of this when I went back to try and find the link. Just wanted to give credit where credit was due!!!


http://groups.google.com/groups?q=s...UTF-8&selm=eUQ7uPJ3$GA.269@cppssbbsa04&rnum=2