Excel "Sumproduct" Totaling formula?
 

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

Excel "Sumproduct" Totaling formula?
 

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)


"Charliec" wrote:

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

Excel "Sumproduct" Totaling formula?
 

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")

In article ,
Charliec wrote:

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

Excel "Sumproduct" Totaling formula?
 

Great, works like a charm! Thanks for the tip.
Charlie

On Thu, 22 Mar 2007 15:40:08 -0700, John wrote:

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)


"Charliec" wrote:

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

************************************************** ****
Charliec

Excel "Sumproduct" Totaling formula?
 

Thanks

Charlie

On Thu, 22 Mar 2007 16:42:36 -0600, JE McGimpsey wrote:

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")

In article ,
Charliec wrote:

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
************************************************** ****
Charliec