Tallying student scores
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. |
Tallying student scores
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. |
Tallying student scores
=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. |
Tallying student scores
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. |
Tallying student scores
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. |
Tallying student scores
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. |
OOPS! Another Hitch
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. |
OOPS! Another Hitch
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] |
OOPS! Another Hitch
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] |
All times are GMT +1. The time now is 03:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com