Tallying student scores

[Drowning in data]

I need to count the number of students who score in specific ranges on tests,
but also meet one other criteria, such as female. I have my spreadsheet set
up so that the criteria (indicated by a single letter such as "f" for female)
are in the first columns on the left, and the scores are entered in the
columns on the right. So, I need to have the spreadsheet count the number of
girls who scored below 60 (on a single test whose scores are in a single
column), for example. I have a sumproduct formula that works for that, but I
can't get a formula to work when I want to count the number of girls who
scored between 61 and 70.

 

[Drowning in data]

=SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59))

I've also modified this for scores above 90 with success. It is when I need
a range that I keep getting error messages.

 

[David Biddulph]

What formula did you use, and what error message did you get?

If =SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59)) works, then I would expect
=SUMPRODUCT(--(B5:B74="F"),--(G5:G74>=61),--(G5:G74<=70)) to work too.

 

[Drowning in data]

This seems to be working!! Thanks a million!

Can you use this?

=SUMPRODUCT(--(B5:B74="F"),--(G5:G74>=61),--(G5:G74<=70))

 

[Drowning in data]

This formula worked, but it also counted females who didn't have test scores
entered (the cell is blank). Because I am recording the results of several
tests on the same spreadsheet, I only want a tally of the females who have
test scores entered and whose scores fall within the specified ranges. It
seems to be considering a blank cell as a zero score. I tried entering NA in
the cell to designate "does not apply" but then it added the student to those
females scoring above 90%. Any suggestions?

 

[Drowning in data]

I left the cells blank and used the <>"" formula and it worked! Thanks!

Take the NAs and whatnot out and try one of these:

=SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59),--(G5:G74>0))

Or probably better, this, since you might have entries of zero you
actually want to count:

=SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59),--(G5:G74<>""))

This formula worked, but it also counted females who didn't have test scores
entered (the cell is blank). Because I am recording the results of several
tests on the same spreadsheet, I only want a tally of the females who have
test scores entered and whose scores fall within the specified ranges. It
seems to be considering a blank cell as a zero score. I tried entering NA in
the cell to designate "does not apply" but then it added the student to those
females scoring above 90%. Any suggestions?

[snipped]