Ravindra BagaleCourses & study guides मराठी Track your progress

Excel COUNT, COUNTA, COUNTBLANK and COUNTIF: Total Students, Absentees and Pass Percentage (with MAX and MIN)

By Last updated:

Suppose you have to choose between two schools, and the first question is: which one has the better pass percentage? To answer it you need to count: how many students, how many were absent, how many passed. In this chapter we build a small marks sheet of 10 students and answer every one of those questions with a formula. These are your first real Excel functions, and you will use them in every analysis you ever do.

What you'll learn in this chapter

  • The five functions every analyst uses
  • Write a formula: = sign, function name, range
  • Read the function tooltip: bold, [optional] and …
  • COUNT counts numbers; COUNTA counts non-empty cells
  • Show a formula with FORMULATEXT
  • Make a Pass/Fail column with IF
  • COUNTIF: count passes and fails
  • Pass percentage, two ways
  • COUNTBLANK: count absentees (blank is not 0)
  • MAX and MIN: top and lowest marks

Practice file: excel-count-countif-practice.xlsx has the marks sheet with every formula (sheet Marks) and the same data without formulas (sheet Your turn).

1. Five functions that every tool uses

Why: whether you use Excel, SQL, Power BI, Tableau or Python, the first questions about any data are almost always the same: how many, how much in total, what is the highest, the lowest, the average. Five functions answer them:

Function Answers Example question
COUNT how many How many students?
SUM how much in total Total marks in maths?
MAX the highest Top marks in science?
MIN the lowest Lowest marks in English?
AVERAGE the typical value Average maths marks?

Learn them well in Excel and you will recognise them everywhere else. This chapter covers COUNT (and its family), MAX and MIN.

2. The marks sheet

Rows 5 to 14 hold 10 students. The pass mark is 35 in every subject.

id name maths science english
1 Ravindra 78 85 66
2 Shraddha 92 88 90
3 Ruhi 34 71 58
4 Shahrukh 56 49 30
5 Amir (blank) 62 47
6 Salman 88 35 72
7 Zoya 67 90 84
8 Ravina 45 28 52
9 Raja 0 40 38
10 Rani 81 76 89

Two cells matter a lot in this chapter:

  1. Amir's maths cell is blank: he was absent for the maths exam.
  2. Raja's maths cell is 0: he appeared and scored zero.

Classroom line

Wherever you see a blank, that student is absent.

Quick way to type it: type 1 and 2 in A5:A6 and drag down for the ids. If you made the name Custom List in the date formats chapter, type Ravindra and drag down for the names.

The marks sheet: blank = absent, 0 = scored zero A B C D E 4 id name maths science english 5 1 Ravindra 78 85 66 6 2 Shraddha 92 88 90 7 3 Ruhi 34 71 58 8 4 Shahrukh 56 49 30 9 5 Amir 62 47 10 6 Salman 88 35 72 11 7 Zoya 67 90 84 12 8 Ravina 45 28 52 13 9 Raja 0 40 38 14 10 Rani 81 76 89 C9 is blank: Amir was absent COUNT skips it, COUNTBLANK counts it, MAX, MIN and AVERAGE ignore it C13 is 0: Raja appeared, scored 0 COUNT counts it, COUNTBLANK does not, MIN of maths becomes 0

Figure 1. The marks sheet. Amir's blank maths cell means absent; Raja's 0 means he appeared and scored zero. Excel treats these two very differently.

3. Writing a formula

Rules:

  1. Every formula starts with =. Without it, Excel treats what you type as plain text.
  2. Then the function name, then brackets: =COUNT(A5:A14).
  3. A5:A14 is a range: the colon means "from A5 to A14", all ten cells.
  4. A comma separates separate items: =COUNT(A5,A6,A7) counts just those three cells.

Classroom line

Always type the equals sign first.

Read the tooltip. As soon as you type =COUNT(, Excel shows a small tooltip box: COUNT(value1, [value2], ...).

  1. The bold part is the argument you are typing right now.
  2. An argument in [square brackets] is optional.
  3. An argument without brackets is compulsory. =COUNT() with nothing inside is not accepted.
  4. ... means you can give more of the same.

Classroom line

If it is written without square brackets, it means you must give that value.

Read the function tooltip =COUNT( COUNT(value1, [value2], ...) value1 in bold the argument you are typing now; no brackets = compulsory [value2] square brackets = optional ... you may give more values =COUNT() empty brackets are not accepted: value1 is compulsory

Figure 2. The tooltip that appears while you type a function: bold = the argument you are on, [ ] = optional, … = you may add more.

4. COUNT: how many numbers

What: COUNT counts the cells that contain numbers (dates are numbers too). It skips text and blank cells.

Formula Result Why
=COUNT(A5:A14) 10 ten ids, all numbers
=COUNT(C5:C14) 9 Amir's blank maths cell is skipped
=COUNT(B5:B14) 0 names are text, not numbers

Total students: count the id column. A subject column misses absent students (9 instead of 10), and the name column contains text, which COUNT does not count at all.

Classroom line

The COUNT function counts only cells that contain numbers; it does not consider blanks.

5. COUNTA: how many cells are not empty

What: COUNTA counts every cell that is not empty: numbers, text, anything. It does not count blank cells.

Formula Result Why
=COUNTA(B5:B14) 10 ten names (text)
=COUNTA(C5:C14) 9 nine maths marks; the blank is not counted

Use COUNT when the column holds numbers, and COUNTA when it holds text, such as names or cities.

COUNT vs COUNTA vs COUNTBLANK B C 4 name maths 5 Ravindra 78 6 Shraddha 92 7 Ruhi 34 8 Shahrukh 56 9 Amir 10 Salman 88 11 Zoya 67 12 Ravina 45 13 Raja 0 14 Rani 81 Function Counts name B5:B14 maths C5:C14 COUNT numbers only 0 9 COUNTA cells that are not empty 10 9 COUNTBLANK empty cells only 0 1 Raja's 0 is a number, so COUNT and COUNTA both count it. Amir's blank (yellow) is counted only by COUNTBLANK. Total students = COUNT of the id column = 10.

Figure 3. On the same maths column: COUNT and COUNTA both give 9 (Raja's 0 is counted), COUNTBLANK gives 1 (Amir). On the name column COUNT gives 0 and COUNTA gives 10.

6. FORMULATEXT: show the formula next to its result

When you check your work, or explain it to someone, it helps to see the formula and the answer side by side.

  1. Your pass count is in I12 with =COUNTIF(F5:F14,"Pass").
  2. In J12 type =FORMULATEXT(I12).
  3. J12 shows the text =COUNTIF(F5:F14,"Pass").

FORMULATEXT works in Excel 2013 and later, including Microsoft 365. If the cell has no formula, it shows #N/A.

7. A Pass/Fail column with IF

To count passes we first need a column that says Pass or Fail. For now, maths only:

  1. In F4 type the header maths result.
  2. In F5 type =IF(C5>=35,"Pass","Fail") and press Enter. Ravindra has 78, so F5 shows Pass.
  3. Drag F5's fill handle down to F14.

IF checks a condition (C5>=35). If it is true you get the first value, otherwise the second. Text results go in "double quotes"; numbers and cell addresses do not.

When you drag the formula down, Excel changes the row number by itself: F6 has =IF(C6>=35,…), F7 has =IF(C7>=35,…). This is called a relative reference.

The results: Pass for Ravindra, Shraddha, Shahrukh, Salman, Zoya, Ravina and Rani; Fail for Ruhi (34), Amir (blank, which IF treats as 0) and Raja (0). The IF chapter goes deeper and checks all three subjects.

8. COUNTIF: count only what matches

What: COUNTIF(range, criteria) counts the cells in a range that match a condition.

What we count Formula Result
Passed in maths =COUNTIF(F5:F14,"Pass") 7
Failed in maths =COUNTIF(F5:F14,"Fail") 3
Scored 80 or more in maths =COUNTIF(C5:C14,">=80") 3
  1. The condition goes in double quotes: "Pass", ">=80".
  2. COUNTIF ignores capital and small letters: "pass" and "Pass" give the same count.
  3. Change a mark and the count updates at once: make Ruhi's maths 40 and the pass count becomes 8.

Classroom line

Check which cell YOUR pass count is in.

Ravindra Bagale's Tip

Don't copy cell addresses from a book or video blindly. If your table starts in row 3 instead of row 5, every range moves. Click the cell you want to use while typing the formula, and Excel writes the correct address for you.

9. Pass percentage

Pass % = students who passed ÷ total students × 100

With the pass count in I12 and total students in I5:

  1. Way 1: =I12/I5*100 gives 70. Read it as 70%.
  2. Way 2: =I12/I5 gives 0.7. Press Ctrl+1 › Percentage (or Home › % button) and it shows 70%.

Way 2 is better when the number feeds a chart or another formula, because the cell really holds a percentage.

IF > COUNTIF > pass percentage B C F 4 name maths maths result 5 Ravindra 78 Pass 6 Shraddha 92 Pass 7 Ruhi 34 Fail 8 Shahrukh 56 Pass 9 Amir Fail 10 Salman 88 Pass 11 Zoya 67 Pass 12 Ravina 45 Pass 13 Raja 0 Fail 14 Rani 81 Pass =IF(C5>=35,"Pass","Fail") in F5, dragged down to F14 =COUNTIF(F5:F14,"Pass") = 7 passed =COUNT(A5:A14) = 10 students =I12/I5 = 0.7, shown as 70% (or =I12/I5*100 = 70)

Figure 4. From marks to pass percentage: IF makes the result column, COUNTIF counts Pass (7), COUNT counts students (10), and 7 ÷ 10 = 70%.

10. COUNTBLANK: count the absentees

What: COUNTBLANK counts empty cells only.

Formula Result Why
=COUNTBLANK(C5:C14) 1 Amir's maths cell is empty
=COUNTBLANK(D5:D14) 0 every student has science marks

0 is not blank. Raja's 0 is a number: COUNTBLANK does not count it, and COUNT does. Delete two more maths marks and COUNTBLANK becomes 3.

Classroom line

COUNTBLANK counts only the blanks.

Ravindra Bagale's Tip

Never type 0 for an absent student "to fill the gap". A 0 is a real score: COUNTBLANK misses the absentee, MIN becomes 0 and AVERAGE goes down. Leave absent cells blank, or type a clear word such as AB and count it with =COUNTIF(C5:C14,"AB").

11. MAX and MIN: top and lowest marks

What Formula Result
Top maths =MAX(C5:C14) 92 (Shraddha)
Lowest maths =MIN(C5:C14) 0 (Raja)
Top science =MAX(D5:D14) 90 (Zoya)
Lowest science =MIN(D5:D14) 28 (Ravina)
Top English =MAX(E5:E14) 90 (Shraddha)
Lowest English =MIN(E5:E14) 30 (Shahrukh)

MAX and MIN skip blank cells (Amir's blank is ignored) but include 0 (Raja's 0 is the lowest maths mark). The same is true for SUM and AVERAGE: =SUM(C5:C14) is 541 and =AVERAGE(C5:C14) is 60.11, an average over 9 marks, not 10.

MAX and MIN: blanks ignored, zeros counted A B C D E 4 id name maths science english 5 1 Ravindra 78 85 66 6 2 Shraddha 92 88 90 7 3 Ruhi 34 71 58 8 4 Shahrukh 56 49 30 9 5 Amir 62 47 10 6 Salman 88 35 72 11 7 Zoya 67 90 84 12 8 Ravina 45 28 52 13 9 Raja 0 40 38 14 10 Rani 81 76 89 15 MAX 92 90 90 16 MIN 0 28 30 Row 15: =MAX(C5:C14) … Row 16: =MIN(C5:C14) … Green = highest, blue = lowest in each subject. Maths MIN is 0: Raja's 0 is a real score (red). Amir's blank (yellow) is skipped by MAX and MIN. SUM(C5:C14) = 541, AVERAGE(C5:C14) = 60.11 (the average is over 9 marks: the blank is skipped)

Figure 5. MAX and MIN on each subject. The blank is ignored; the 0 is counted, so the lowest maths mark is 0.

12. The whole chapter on one page

  1. Five everyday functions: COUNT, SUM, MAX, MIN, AVERAGE.
  2. A formula starts with =; A5:A14 means A5 to A14.
  3. Tooltip: bold = current argument, [ ] = optional, … = more allowed.
  4. COUNT counts numbers only; total students = COUNT of the id column.
  5. COUNTA counts non-empty cells (text too); blanks are not counted.
  6. =FORMULATEXT(cell) shows a cell's formula as text.
  7. =IF(C5>=35,"Pass","Fail"), dragged down, makes the result column.
  8. =COUNTIF(F5:F14,"Pass") counts passes; criteria in quotes.
  9. Pass % = passes ÷ total × 100, or passes ÷ total with % format.
  10. COUNTBLANK counts empty cells; 0 is not blank.
  11. MAX and MIN ignore blanks but include 0.

13. Try at home

Try at home

Task 1: build the sheet

  1. Type the 10-student marks sheet from section 2 (or open the Your turn sheet of the practice file).
  2. Find total students, maths marks entered and names entered.

Task 2: pass and fail

  1. Make the maths result column with IF.
  2. Count Pass and Fail, and work out the pass percentage both ways.
  3. Change Ruhi's maths mark to 40. Which numbers change?

Task 3: absent vs zero

  1. Count absentees in each subject with COUNTBLANK.
  2. Delete Raja's 0. What happens to COUNTBLANK, MIN and AVERAGE for maths?

Task 4: top and lowest

  1. Find the top and lowest marks in each subject.
  2. Count how many students scored 80 or more in science and in English.

Recap

In short

  1. = first, then function, then range.
  2. COUNT = numbers; COUNTA = not empty; COUNTBLANK = empty.
  3. Total students: COUNT the id column.
  4. IF makes Pass/Fail; COUNTIF counts them.
  5. Pass % = passes ÷ total (× 100, or % format).
  6. Blank = absent, 0 = scored zero; never mix them.
  7. MAX and MIN skip blanks but count zeros.
  8. FORMULATEXT shows the formula behind a result.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: COUNT, COUNTBLANK and pass percentage. Example data table with Students who wrote the test: 4; Absent students: 1; Pass percentage: 60.0%.
Card 1 of 2: COUNT, COUNTBLANK and pass percentage · Download PNG
Revision card: Pass/Fail, blanks and FORMULATEXT. Example data table with Pass or Fail for ID 1: Pass; Blank or 0 for an absent student?: 1 absent; Show the formula behind F2: =IF(C2>=35,"Pass","Fail").
Card 2 of 2: Pass/Fail, blanks and FORMULATEXT · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Pass or fail (Easy): "Pass" if B2 is at least 40, else "Fail".
  2. Total (Easy): B2 plus C2.
  3. Passed score 1 (Easy): How many in B2:B11 are 40 or more?
  4. Passed both (Medium): TRUE if B3 and C3 are both at least 40.
  5. Passed either (Medium): TRUE if B4 or C4 is at least 75.
  6. Grade (Medium): Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible (Medium): "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both (Hard): How many have both scores at least 40?
  9. Pass percentage (Hard): Share passing score 1, rounded to 2 decimals.
  10. Nested IF band (Hard): "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer (Hard): Name with the highest score 1.

See all practice tasks in the Task Hub →