SUMPRODUCT question

[cpliu]

Thanks to the help from this newsgroup I found SUMPRODUCT useful in
extracting total number of text data but how does it work with a value?

For example, I'd like to find out how many people choose 5, how many choose
4, 3, 2, 1 in a rating system.

SUMPRODUCT(E2:E300=5) comes up with 0 and I then put number 5 in cell C301
as a model cell for it to compare but SUMPRODUCT(E2:E300=C301) comes up
with 0 too.

How do I tell it to find the total numbers of cells with value 5 in all E2
to E300?


Thanks,

cpliu

 

[JulieD]

Hi

for this, just use the COUNTIF function
=COUNTIF(E2:E300,5)

Cheers
JulieD

 

[Frank Kabel]

Hi
try either
=COUNTIF(E2:E300,5)
or
=COUNTIF(E2:E300,C301)

using SUMPRODUCT use
=SUMPRODUCT(--(E2:E300=5))
or
=SUMPRODUCT(--(E2:E300=C301))
you have to coerce the boolean values to a number. Therefore the '--'

 

[Leo Heuser]

cpliu

E2:E300=5

will return an array of True/False values,
e.g. {True,True,False,True,False,.......} etc.

In order to convert True to 1 (one) and False
to 0 (zero), one way is to add zero to the
array like this:

(E2:E300=5)+0

Now the array will be {1,1,0,1,0,.......} etc

So

SUMPRODUCT((E2:E300=5)+0)
will do the job.
Another way frequently seen in these groups,
is the "double negation"

SUMPRODUCT(--(E2:E300=5))

 

[cpliu]

Thank you all for the quick response. It works now.

Best regards,

cpliu

 

[cpliu]

I have another sumproduct question:

For example:
date amount
1/3/2004 $1,000.00
1/5/2004 $1,000.00
1/12/2004 $1,200.00
2/1/2004 $1,000.00
2/12/2004 $1,500.00
2/13/2004 $1,000.00
2/14/2004 $1,300.00

I'd like to find out the amount in Janary and Feb.:

I have =SUMPRODUCT((A2:A8>12/21/2003)*(A2:A8<2/1/2004)*(B2:B7)) but it
comes up $0. How do I define a period of date?

Thanks for the help,


cpliu

 

[Peo Sjoblom]

One way

=SUMPRODUCT((A2:A8>--"12/21/2003")*(A2:A8<--"3/1/2004")*(B2:B8))

I assume the B2:B7 was a typo in your example

better would probably be to use

=SUMPRODUCT((A2:A8>=DATE(2004,1,1))*(A2:A8<=DATE(2004,2,29))*(B2:B8))

or

=SUMPRODUCT(--(A2:A8>=DATE(2004,1,1)),--(A2:A8<=DATE(2004,2,29)),B2:B8)