Count a range that matches the heading

J

JanetP

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?
 
T

T. Valko

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:K2>0))

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.
 
D

Dave

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top