Excel Grade Formula Using COUNTIF: Setup and Examples

Formulas & functions · Updated November 20, 2026

To assign a letter grade with COUNTIF, put the score in B2, the grade cutoffs 60, 70, 80, and 90 in F2:F5, and the matching grade labels F, D, C, B, and A in G2:G6. Then use =IF(B2="","",INDEX($G$2:$G$6,COUNTIF($F$2:$F$5,"<="&B2)+1)). This returns the grade for B2 and leaves the result blank if B2 is empty.

What COUNTIF Does in a Grade Formula

COUNTIF is a counting function: it counts cells in a range that meet one condition. In the formula above, it counts how many cutoffs are less than or equal to the student’s score. The count determines which position to retrieve from the grade-label list. For a score of 85, three cutoffs—60, 70, and 80—are at or below the score, so COUNTIF returns 3. Adding 1 gives position 4, and the fourth label in G2:G6 is B.

This is a useful distinction: COUNTIF does not independently know what an A or B means. The cutoff table supplies your grading policy, and COUNTIF helps identify the appropriate row. The formula assumes scores use a 0–100 scale and that meeting a cutoff earns that grade. Thus, 90 earns A, 80 earns B, and a score below 60 earns F. Change the cutoffs or labels to match your course rules.

The function’s general syntax is =COUNTIF(range,criteria). The range is the cells to inspect; the criteria is the condition to test. Here the criterion is built by joining the less-than-or-equal operator to the score in B2. The dollar signs keep the cutoff range fixed when you copy the formula to other rows.

Set Up the Cutoff and Grade Table

Use a small lookup table rather than typing different cutoffs into each student’s formula. For this example, enter the following values:

  • F2: 60 and G2: F
  • F3: 70 and G3: D
  • F4: 80 and G4: C
  • F5: 90 and G5: B
  • G6: A, with no additional cutoff in F6

Enter each cutoff as a number, not text, and keep the cutoff values in ascending order. Put each grade beside its lower boundary: 60 goes with D, 70 with C, 80 with B, and 90 with A. The formula’s extra first label, F in G2, covers scores below the first cutoff. That is why the threshold range contains four cells while the grade range contains five.

In a real workbook, scores might be in B2:B31 and the formula results in C2:C31. Type the formula in C2, then fill it down. For a score of 74, the formula counts 60 and 70, adds 1, and returns the third label, C. For 59 it returns F; for 90 it returns A. These examples assume the table above and exact whole-number cutoffs.

Keeping the policy in cells also makes revisions easier. If the instructor changes the B cutoff from 80 to 82, edit F4 once; all the calculated grades update. Make sure the labels and cutoff rows still correspond after any change. A misplaced grade label can give plausible-looking but incorrect results.

Fill the Formula Down and Use the Shortcut

Excel does not have a special keyboard shortcut that creates a COUNTIF grading formula for you. The shortcut applies after you have entered the formula in the first result cell. In Excel for Microsoft 365 on Windows, select the formula cell and the cells below it in the same column, then press Ctrl+D to Fill Down. Excel copies the top cell’s formula into the selected cells.

On Excel for Mac, select the formula cell and the destination cells, then use Command+D for Fill Down. If the shortcut is unavailable because of a keyboard or system configuration, use the ribbon: Home > Fill > Down. You can also drag the fill handle—the small square at the lower-right corner of the selected cell—down the result column.

The references are designed to copy safely: B2 changes to B3, B4, and so on, while $F$2:$F$5 and $G$2:$G$6 remain fixed. If Excel changes the table references as you fill, check that the dollar signs are present. If you have a large or changing list of students, converting the data range into an Excel table can make formulas easier to extend when new rows are added.

Count How Many Students Earn Each Grade

Assigning grades and tallying grades are related but different jobs. Once letter grades appear in column C, COUNTIF can count how many times a particular letter occurs. With grades in C2:C31 and the grade to count in E2, enter =COUNTIF($C$2:$C$31,E2) in F2. If E2 contains B, the formula returns the number of B grades in the range.

Copy that tally formula down beside a list of grade labels in E2:E6, such as A, B, C, D, and F. The criteria reference E2 is relative, so it changes to E3 in the next row; the grade range stays fixed. This is often what people mean when they search for a “grade formula using COUNTIF”: COUNTIF can either help map scores to labels through a cutoff table or count already assigned labels, but the formulas serve different purposes.

For the tally to be meaningful, the counted range should contain only the grade results—not headers, notes, or unrelated text. COUNTIF is not case-sensitive, so an uppercase b and lowercase b are treated as the same criterion. A grade with a trailing space, such as “B ”, may not match a clean B. If totals seem too low, inspect the source cells for extra spaces or inconsistent entries.

Handle Blanks, Boundaries, and Score Formats

The opening formula includes an IF check so an empty score cell stays visually empty. Without that check, Excel may treat an empty referenced cell like zero in this calculation and assign the lowest grade. A blank result is usually less confusing than showing F for work that has not been entered. The IF check only detects a genuinely empty cell; a cell containing a formula that returns an empty string can require extra care depending on how the source data is built.

Boundary rules matter. With the example cutoffs, a score of exactly 70 receives C, because the formula counts cutoffs less than or equal to the score. If your policy says a grade begins above a cutoff rather than at it, adjust the table or comparison logic to reflect that rule. Before distributing grades, test values just below, exactly at, and just above every boundary—for example 69, 70, and 71.

Also confirm that the score scale matches the cutoffs. A cell displaying 85% may contain the underlying value 0.85, not 85. Comparing 0.85 against cutoffs of 60 to 90 will return F. Either enter scores as numbers from 0 to 100, or build a cutoff table on the same decimal scale as percentage values. Formatting a cell as a percentage changes how it looks, not the underlying number used in comparisons.

Decide how to treat extra decimal places. If a score is 79.6 and the B cutoff is 80, the formula returns C unless the score is rounded first or the grading policy specifies another rule. Do not add rounding just to make a result look expected; apply the actual course policy consistently. You can also test for impossible entries, such as scores above 100, before relying on the grade output.

COUNTIF Compared with Other Grade Formulas

For a straightforward set of ascending lower-bound cutoffs, COUNTIF with INDEX is a flexible method because the thresholds and labels live in a table. It is especially handy when you want to count how many cutoffs a score meets. The downside is that the table needs a clear layout and the formula can be less immediately recognizable to someone who expects a simple lookup.

Another option is LOOKUP, which is designed to find the largest lookup value that does not exceed the score in an ascending list. With scores in B2, thresholds in F2:F5 (60, 70, 80, 90), and corresponding labels in G2:G5 (D, C, B, A), the formula =LOOKUP(B2,$F$2:$F$5,$G$2:$G$5) returns B for 85. This layout needs no separate F label because a score below 60 has no matching threshold; decide how to handle that case if low scores are possible.

Nested IF formulas can work for a small number of grades, but they put the grading rules inside the formula. That makes policy changes harder to review and can produce long formulas. XLOOKUP is another option in Microsoft 365 and Excel 2021 or later, but it is not necessary for this task. Choose the approach that makes the cutoff rules easiest for you and other workbook users to inspect.

Common Errors and Practical Checks

If the result is unexpectedly low, first check whether the score is numeric and whether its scale matches the cutoff table. Then verify that the cutoffs are ascending and the labels sit in the intended order. If the formula shows an error, confirm that the COUNTIF and INDEX ranges are valid, that the grade list has one more entry than the cutoff list, and that the quotation marks around the criterion are straight double quotes.

Use a small test set before filling the formula through a class list. Include a blank, a score below the first cutoff, a score at every cutoff, and a score between cutoffs. Compare the returned grades with the written grading policy. This catches off-by-one mistakes, which are common when the first label represents all values below the first threshold.

For auditability, keep the cutoff table visible and label its columns, for example “Minimum score” and “Grade.” Protecting formula cells can help prevent accidental edits in a shared workbook, but it does not replace checking the results. If the grading policy changes, update the table and retest the boundaries rather than editing individual results manually.

Frequently Asked Questions

Can COUNTIF assign a letter grade by itself?
COUNTIF supplies the count used to identify a grade, but the cutoff table and INDEX function map that position to a letter. COUNTIF can also count existing letter grades directly.

What is the simplest COUNTIF formula to count B grades?
With letter grades in C2:C31, enter =COUNTIF($C$2:$C$31,"B") in the cell where you want the total. It counts cells equal to B in that range.

Why does a blank score show F?
A formula without a blank check can evaluate an empty score reference like zero for this comparison. Use the IF-wrapped version shown above to return a blank until a score is entered.

Can I use different grade cutoffs?
Yes. Replace the example cutoff values with your policy’s minimum scores, keep them in ascending order, and align each cutoff with its grade label. Test exact boundary scores after changing the table.

Is there a shortcut to enter the formula in every row?
Enter it once in the first result cell, select that cell and the cells below, and press Ctrl+D on Windows or Command+D on Mac. This fills the formula down; it does not create the formula automatically.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.