SOMEPRODUCT SUMIF SYNTAX (THEORY QUESTION)

planetpj

Active Member
Joined
Jun 25, 2002
Messages
346
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
BCDEFG
1PJ2PJPaul28
2Jim3
3PJ4
4Al5
5Paul22
6
7Harlan'sGrove'sFormula
828
928
Sheet1
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

Active Member
Joined
Jun 25, 2002
Messages
346
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,151
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top