Count Students by Category and Grade Range with Excel COUNTIFS
Question details
Count students by a classification, such as REG or ELL, and by a specified grade-average range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to create summary counts for student groups across multiple grade-average bands.
- Observed behavior
- A count is needed only when both the student category and the minimum and maximum grade criteria are met.
Confirm that the CategoryRange and AverageRange contain the same number of rows and that grade averages are stored as numeric values. Decide whether each range endpoint should be included before entering the criteria.
Use COUNTIFS for a student category and grade-average band
Use this method when each student has a category and a numeric average, and you need to count records that meet all conditions.
COUNTIFS evaluates multiple criteria at the same time. Use one category criterion and two criteria against the average column to define the lower and upper limits of each grade band.
In your worksheet, identify the cells containing classifications, such as REG or ELL, and the cells containing each student's grade average. For example, use $B$2:$B$100 as CategoryRange and $C$2:$C$100 as AverageRange.
Select the summary cell and enter =COUNTIFS($B$2:$B$100,"REG",$C$2:$C$100,">=0",$C$2:$C$100,"<=54") to count REG students with averages from 0 through 54.
Edit "REG" to another classification, such as "ELL", and replace 0 and 54 with the lower and upper scores for the next grade range. For example, use ">=55" and "<=69" for a 55–69 band.
If your categories or limits are stored in worksheet cells, replace typed criteria with cell references, such as =COUNTIFS($B$2:$B$100,E2,$C$2:$C$100,">="&F2,$C$2:$C$100,"<="&G2), then fill the formula down or across.
Count Student Categories and Grade Bands in WPS Spreadsheets
WPS Spreadsheets supports COUNTIFS, so you can build a category-and-grade summary using the same multi-criteria formula structure. Enter your student data, reference the category and average columns, and fill formulas across your report.
- 1. Open the student worksheet: Open the .xlsx or .xls file in WPS Spreadsheets and locate the category and grade-average columns.
- 2. Select a summary result cell: Click the cell where the count for a category and grade band should appear.
- 3. Enter a COUNTIFS formula: Type a formula such as =COUNTIFS($B$2:$B$100,"REG",$C$2:$C$100,">=0",$C$2:$C$100,"<=54") and press Enter.
- 4. Reference summary cells for flexible criteria: Store the category, minimum score, and maximum score in cells, then join the operators to the limits with formulas such as ">="&F2 and "<="&G2.
- 5. Fill the summary table: Drag the fill handle to copy the formula to other category rows or grade-range columns, then verify the resulting counts.
Frequently Asked Questions
Why does COUNTIFS need the average range twice?
The average range is used once for the lower-limit test and once for the upper-limit test. For example, AverageRange,">=0" and AverageRange,"<=54" ensure that the same student's average falls within both limits.
How can I count ELL students with averages from 70 to 84?
Change the category and score criteria in the formula: =COUNTIFS(CategoryRange,"ELL",AverageRange,">=70",AverageRange,"<=84"). Replace the named ranges with your actual worksheet ranges if needed.
Can I use cells instead of typing REG and score limits into every formula?
Yes. If E2 contains the category, F2 the minimum score, and G2 the maximum score, use =COUNTIFS(CategoryRange,E2,AverageRange,">="&F2,AverageRange,"<="&G2). This makes the summary easier to update.
What happens if a student has a blank or text value for the grade average?
A blank or nonnumeric grade average will not meet numeric criteria such as ">=0" and "<=54". Check the average column and convert text-formatted numbers to numeric values when appropriate.
