Standards-based grading tracks how many learning standards a student has mastered. Count the mastered marks over the total standards assessed — COUNTIF turns a row of marks into a mastery percentage.
The example
9 of 12 standards mastered.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Mastered | 9 |
| 3 | Assessed | 12 → 75% |
The formula
The formula:
How it works
How it works:
COUNTIF(marks, "M")counts the standards marked Mastered.- Divide by
COUNTA(marks)— the total standards assessed — for the rate. - Track proficiency levels (e.g. 4/3/2/1) with
COUNTIF(marks, ">=3")for “proficient or above.” - A class view per standard uses
COUNTIFSacross students to find which standards need reteaching.
By student and by standard: count across a row for one student’s overall mastery, and down a column for how the whole class did on one standard. The column view — =COUNTIF(standard_col, "M") / students — instantly flags standards where most of the class is below mastery and reteaching is needed.
Try it: interactive demo
Marks: M mastered, P progressing, N not yet.
Variations
Proficient or above
Levels 3–4:
Per standard (class)
Down a column:
Standards not yet met
Remaining work:
Pitfalls & errors
Consistent marks. Use the same code (M / 3 / etc.) everywhere or COUNTIF misses some.
Row vs column. Across = one student; down = one standard across the class.
Blanks. COUNTA skips empty cells — decide if unassessed standards count.
Practice workbook
Frequently asked questions
How do I calculate standards mastery in Excel?
How do I count 'proficient or above' on a 4-point scale?
How do I see class performance on one standard?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class