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

Danny J wrote...
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?

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

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

and press [F9] to evaluate it. What do you get? Do you understand why
it looks the way it does?
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!

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)


"Danny J" wrote in message
...
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

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" wrote in message
...
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

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.

In article ,
"JulieD" wrote:

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

Hi John

thanks for the clarification

Cheers
JulieD
"JE McGimpsey" wrote in message
...
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.

In article ,
"JulieD" wrote:

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

Thanks!!!!!

Much clearer now :-)


"JulieD" wrote in message
...
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" wrote in message
...
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

Thanks Guys!


"JulieD" wrote in message
...
Hi John

thanks for the clarification

Cheers
JulieD
"JE McGimpsey" wrote in message
...
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.

In article ,
"JulieD" wrote:

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