counting spercific charaters in selected cells
 

I have a worksheet which contains a number of pupils results with gaps
inbetween then. I want to put a formula at the end of each row to calculate
how many people have A*-C grade.

However I can only seem to do this for a selected range instead of choosing
the spercific cells I want to include. This is a copy of the formula I have
for the entire range;
{=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1: J1="C"), 1, 0))}

However, as I said I need it to apply to just cells A1,C1,E1. I have tried
entering this as a comma seperated list eg, (A1,C1,E1="A*). However this
doesn't work as it claims there is a value error (Like everything that goes
wrong on Excel)

Any ideas? I have put the correct syntax and the array is in the correct
place. I have many otherworksheets with this formula working through an
entire range but this one is different as I need it through specified cells.

Cheers

Tom

counting spercific charaters in selected cells
 

On Mon, 9 Jun 2008 07:14:03 -0700, Tom5 wrote:

I have a worksheet which contains a number of pupils results with gaps
inbetween then. I want to put a formula at the end of each row to calculate
how many people have A*-C grade.

However I can only seem to do this for a selected range instead of choosing
the spercific cells I want to include. This is a copy of the formula I have
for the entire range;
{=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1 :J1="C"), 1, 0))}

However, as I said I need it to apply to just cells A1,C1,E1. I have tried
entering this as a comma seperated list eg, (A1,C1,E1="A*). However this
doesn't work as it claims there is a value error (Like everything that goes
wrong on Excel)

Any ideas? I have put the correct syntax and the array is in the correct
place. I have many otherworksheets with this formula working through an
entire range but this one is different as I need it through specified cells.

Cheers

Tom

Here's one way:


=SUMPRODUCT((A1={"A*";"A";"B";"C"})+(C1={"A*";"A"; "B";"C"})+(E1={"A*";"A";"B";"C"}))

Of course, I would enter the grades you are searching for into a Named range,
and then use this simpler appearing (and editable) formula:

=SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades))

Make sure there are no blank cells in the named range "Grades"
--ron