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 wrote:
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.

Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

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

"smartin" wrote:

Drowning in data wrote:
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.

Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

Can you use this?

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

Drowning in data wrote:
=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.

"smartin" wrote:

Drowning in data wrote:
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.
Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

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.
--
David Biddulph

"Drowning in data" wrote in
message ...
=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.

"smartin" wrote:

Drowning in data wrote:
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.

Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

This seems to be working!! Thanks a million!

"smartin" wrote:

Can you use this?

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

Drowning in data wrote:
=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.

"smartin" wrote:

Drowning in data wrote:
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.
Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

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?

"smartin" wrote:

Can you use this?

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

Drowning in data wrote:
=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.

"smartin" wrote:

Drowning in data wrote:
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.
Let's see your working SUMPRODUCT for score < 60. I think it can be made
to work for the second case as well.

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

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

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

Drowning in data wrote:
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?

"smartin" wrote:

Can you use this?

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

Drowning in data wrote:
=SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59))

[snipped]

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

"smartin" wrote:

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

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

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

Drowning in data wrote:
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?

"smartin" wrote:

Can you use this?

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

Drowning in data wrote:
=SUMPRODUCT(--(B5:B74="F"),--(G5:G74<=59))

[snipped]