Help understanding SUMPRODUCT

[normpam]

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!

 

[steve the fish]

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]

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]

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]

did you read the link I supplied? It gives a good starting explanation

 

[steve the fish]

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

 

[Aladin Akyurek]

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]

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.

 

[Aladin Akyurek]

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

You are welcome. Here is what I believe to be a first write-up on this function and its use in formulas:
http://www.mrexcel.com/forum/hall-f...s-explanation-condition-driven-computing.html

and a link on the -- bit:
http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html