Count a range that matches the heading
 

I have a spreadsheet that is tracking attendance and grades for a class. For
instance, column a has a list of names of people in the class; column d keeps
track of the attendance for 6/11, column e keeps track of the grade for 6/11.
column f keeps track of the attendance for 6/18; column g keeps track of the
grade for 6/18, etc. In order words, there are two columns per week. I am
using a sumif formula for the attendance that looks like
=sumif($d$6:$k$6,b$6,$d7:$k7) and that is working very well. However, Im
having problems with the grades. Not everyone takes a test every week. I
want to be able to count the number of test scores for any given person €“
some may have 2, some 5, some 10, etc. How do I get a count of tests taken
for each person?

Count a range that matches the heading
 

So, sounds like you want to get a count of every other cell in the row that
contains a test grade (number?).

If there aren't too many cells involved the easiest way is:

=COUNT(E2,G2,I2,K2)

If there are a lot of cells involved try this:

=SUMPRODUCT(--(MOD(COLUMN(E2:K2)-COLUMN(E2),2)=0),--(E2:K20))

This does the exact same thing as the above COUNT formula.

I'm assuming that if a person does not take a test for a particular date
then that cell is left empty and the grades will be positive numbers greater
than 0.

--
Biff
Microsoft Excel MVP


"JanetP" wrote in message
...
I have a spreadsheet that is tracking attendance and grades for a class.
For
instance, column a has a list of names of people in the class; column d
keeps
track of the attendance for 6/11, column e keeps track of the grade for
6/11.
column f keeps track of the attendance for 6/18; column g keeps track of
the
grade for 6/18, etc. In order words, there are two columns per week. I
am
using a sumif formula for the attendance that looks like
=sumif($d$6:$k$6,b$6,$d7:$k7) and that is working very well. However, I'm
having problems with the grades. Not everyone takes a test every week. I
want to be able to count the number of test scores for any given person -
some may have 2, some 5, some 10, etc. How do I get a count of tests
taken
for each person?

Count a range that matches the heading
 

Hi,
What sort of data do you use to mark attendance? (text or number)
What sort of data do you use to enter grades? (text or number)
What do you mean by "count the number of test scores"
Do you mean count 1 for each test, regardless of grade?
Regards - Dave.