# SOMEPRODUCT SUMIF SYNTAX (THEORY QUESTION)

#### planetpj

##### Active Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

##### MrExcel MVP
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.

#### IML

##### MrExcel MVP
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
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

##### Active Member

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,025
Messages
5,835,016
Members
430,332
Latest member
Charly_Moon

### 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.

### Which adblocker are you using?

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

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