Frank, Sumproduct, it works but I am confused

[Danny J]

Hi Frank,

Thanks for your help. The formula works but I am unclear on the non
classical use of Sumproduct. Looking at the web page you cite an example to
count the number of Ford cars sold in June
=sumproduct((a1:a10="ford")*(b1:b10="June")). However there are 4 instances
of the word Ford in column A and 7 instances of June in column C so why does
return a value of 3 (which is correct for the number of Fords sold in June)
rather than 4*7=28?

Sorry if I am being stupid.

Thanks,

Danny

 

[hrlngrv - ExcelForums.com]

Danny J wrote..

Thanks for your help. The formula works but I am unclear on th no
classical use of Sumproduct. Looking at the web page you cite a
example to count the number of Ford cars sold in Jun
=sumproduct((a1:a10="ford")*(b1:b10="June")). However there ar

instances of the word Ford in column A and 7 instances of June i
column C so why does return a value of 3 (which is correct fo th
number of Fords sold in June) rather than 4*7=28

Deconstruct it. That is, select the cell containing this formula
press [F2] to go into Edit mode, highlight the critical portio

(a1:a10="ford")*(b1:b10="June"

and press [F9] to evaluate it. What do you get? Do you understand wh
it looks the way it does

 

[Bob Phillips]

Danny,

Later in that paper, in the section entitled SUMPRODUCT Explained, it
explains it. It doesn't take an example of Ford and June, but you should be
able to work it out from there. As the paper says, this is not the classical
use of SUMPRODUCT, but an evolved, more useful use to facilitate multiple
conditional tests.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi Danny

i understand the SUMPRODUCT function this way - each element (ie
a1:A10="ford") of the function is evaluated to either true (1) or false (0)
FIRST, these results are multiplied together to give a combined true / false
result of the full statement and then the results of this are added together
to give the count of records meeting both criteria.

so taking the "Ford / June" example
(a1:a10="Ford")
A1=0;A2=1;A3=1;A4=1;A5=1;A6=0;A7=0;A8=0;A9=0;A10=0
so you end up with 0;1;1;1;1;0;0;0;0;0

then take (b1:b10="June")
B1=0;B2=1;B3=1;B4=0;B5=1;B6=1;B7=1;B8=1;B9=0;B10=1
so you end up with 0;1;1;0;1;1;1;1;0;1

now multiply these together to get a combined true / false on the full
statement
0*0;1*1;1*1;1*0;1*1;0*1;0*1;0*1;0*0;0*1
which equals
0;1;1;0;1;0;0;0;0;0
now add these together as we're counting how many meet both criteria and you
get your answer of 3.

Hope this helps
Cheers
JulieD

 

[JE McGimpsey]

Almost. The Boolean result (TRUE/FALSE) of each comparison in the array
isn't translated to 1/0. If you just have a boolean array, SUMPRODUCT
will return 0, as it treats all non-numeric values as 0.

That's the reason using -- in front of the boolean comparison is
necessary - to coerce TRUE/FALSE to 1/0. Using any other math function
does the same thing - e.g., (A1:A10="ford")*1.

If you instead multiply the arrays before handing them off to
SUMPRODUCT():

=SUMPRODUCT((conditional1)*(conditional2))

the math operation coerces both boolean arrays to numeric before
multiplying, then sends the result to SUMPRODUCT() which adds them.

 

[JulieD]

Hi John

thanks for the clarification

Cheers
JulieD

 

[Danny J]

Thanks!!!!!

Much clearer now

Hi Danny

i understand the SUMPRODUCT function this way - each element (ie
a1:A10="ford") of the function is evaluated to either true (1) or false (0)
FIRST, these results are multiplied together to give a combined true / false
result of the full statement and then the results of this are added together
to give the count of records meeting both criteria.

so taking the "Ford / June" example
(a1:a10="Ford")
A1=0;A2=1;A3=1;A4=1;A5=1;A6=0;A7=0;A8=0;A9=0;A10=0
so you end up with 0;1;1;1;1;0;0;0;0;0

then take (b1:b10="June")
B1=0;B2=1;B3=1;B4=0;B5=1;B6=1;B7=1;B8=1;B9=0;B10=1
so you end up with 0;1;1;0;1;1;1;1;0;1

now multiply these together to get a combined true / false on the full
statement
0*0;1*1;1*1;1*0;1*1;0*1;0*1;0*1;0*0;0*1
which equals
0;1;1;0;1;0;0;0;0;0
now add these together as we're counting how many meet both criteria and you
get your answer of 3.

Hope this helps
Cheers
JulieD

 

[Danny J]

Thanks Guys!

Hi John

thanks for the clarification

Cheers
JulieD