# Help understanding SUMPRODUCT

#### normpam

##### Active Member
B
AREA
South
South
South
North
East
North

=COUNTIF(B4:B9,"south")*1 - this formula evaluates correctly as '3'

I also see that the following formula evaluates to '3'"

=SUMPRODUCT(--(B4:B9="South"))

I thought that SUMPRODUCT works on an 'array' of cells such that you can take a qty from once cell and multiply it by a price in another, and have this done for a whole group of cells using SUMPRODUCT, but in the second formula above, SUMPRODUCT seems to work just like a 'COUNTIF', though it is not taking one value and multiplying it by another.

Would very much appreciate if someone could help me better understand this formula!

Thanks!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### steve the fish

##### Well-known Member
That produces an array of True/False values

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

the double negative forces excel to coerce them into numbers (in excel 0 = False, 1 = True)

{1;1;1;0;0;0}

the sumproduct then sums this array

3

#### BarryL

##### Well-known Member
SUMPRODUCT is a terrific and versatile formula. In the above case it goes through B4:B9 assigning TRUE if the cell = "South" and FALSE if it doesn't. the "--" is just to convert the values from logical (TRUE/FALSE) to numerical(1/0) and then it adds the numerical values hence you get 3.

https://exceljet.net/excel-functions/excel-sumproduct-function

#### normpam

##### Active Member
thanks much... what I still don't quite understand is this... does SUMPRODUCT work in two different ways? The simplest way I see is that it does the following:

B C
10 5
20 3
5 10

=sumproduct(b3.b5*c3.c5) takes each qty from column B and multiplies it by the price in column C. But in my other example you addressed above, what is the SUMPRODUCT multiplying, if not one column of values by another?

#### BarryL

##### Well-known Member
did you read the link I supplied? It gives a good starting explanation

#### steve the fish

##### Well-known Member
In this case you have two arrays so it needs a multiplication. In the first you only had one array.

{10;20;5}
{5;3;10}

when multiplied (10x5,20x3,5x10):

{50;60;50}

when summed:

160

##### MrExcel MVP
B
AREA
South
South
South
North
East
North

=COUNTIF(B4:B9,"south")*1 - this formula evaluates correctly as '3'

I also see that the following formula evaluates to '3'"

=SUMPRODUCT(--(B4:B9="South"))

I thought that SUMPRODUCT works on an 'array' of cells such that you can take a qty from once cell and multiply it by a price in another, and have this done for a whole group of cells using SUMPRODUCT, but in the second formula above, SUMPRODUCT seems to work just like a 'COUNTIF', though it is not taking one value and multiplying it by another.

Would very much appreciate if someone could help me better understand this formula!

Thanks!

1) no need for the *1 bit involving the COUNTIF formula:

=COUNTIF(B4:B9,"south")

2)

=SUMPRODUCT(--(B4:B9="South"))

has just one term, so there is nothing to pairwise multiply for its product component. But it must sum the evaluation set of the term:

--(B4:B9="south")

>>

--{TRUE;TRUE;FALSE,TRUE;...}

>>

{1;1;0;1;...}

>> (which is so far)

3

Note that TRUE = 1 and FALSE = 0 in Excel.

#### normpam

##### Active Member
Thanks! just need to let this sink in a bit...

1) no need for the *1 bit involving the COUNTIF formula:

=COUNTIF(B4:B9,"south")

2)

=SUMPRODUCT(--(B4:B9="South"))

has just one term, so there is nothing to pairwise multiply for its product component. But it must sum the evaluation set of the term:

--(B4:B9="south")

>>

--{TRUE;TRUE;FALSE,TRUE;...}

>>

{1;1;0;1;...}

>> (which is so far)

3

Note that TRUE = 1 and FALSE = 0 in Excel.

Replies
5
Views
166
Replies
5
Views
233
Replies
3
Views
293
Replies
2
Views
380
Replies
13
Views
2K

### Forum statistics

1,190,652
Messages
5,982,124
Members
439,756
Latest member
alice128 ### 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