Excel "Sumproduct" Totaling formula?

C

Charliec

I have three columns in a spreadsheet that can contain "A", "Y", "C",
respectively. I'm trying to get a total of each column as to how many
time the A,Y, C, occurs in each column - there is a separate column
for each letter. I tried setting up the formula as follows:

=SUMPRODUCT(C2:C24="A")

But, even though I have an A in some of the cells from CD:C24, the
total still produces a 0 total.

Any suggestions on the formula is appreciated.

Thanks
Charlie
******************************************************
Charliec
 
J

John

Have you tried count if - =countif(C2:C24,"A")
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
J

JE McGimpsey

SUMPRODUCT ignores non-numeric values like TRUE/FALSE so you have to
coerce your calculation into 1/0, e.g.:

=SUMPRODUCT(--(C2:C24="A"))

However, for one column at a time, you might as well use

=COUNTIF(C2:C24,"A")
 
C

Charliec

Great, works like a charm! Thanks for the tip.
Charlie
Have you tried count if - =countif(C2:C24,"A")
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
Click to expand...
******************************************************
Charliec
 
C

Charliec

Thanks

Charlie
SUMPRODUCT ignores non-numeric values like TRUE/FALSE so you have to
coerce your calculation into 1/0, e.g.:

=SUMPRODUCT(--(C2:C24="A"))

However, for one column at a time, you might as well use

=COUNTIF(C2:C24,"A")
Click to expand...
******************************************************
Charliec
 
Top