Count Functions
Introduction: Why Learn Count Functions?
In real-life spreadsheets, you often need to count things — like how many students passed, how many orders were placed, how many entries are blank, and so on.
Google Sheets gives us powerful COUNT functions that help us analyze data quickly with conditions or without.
Step 1: Create This Sample Table
We'll use a sample dataset of students and their marks.
| Name | Subject 1 | Subject 2 | City |
|---|---|---|---|
| Aayush | 78 | 82 | Delhi |
| Riya | 55 | 35 | Mumbai |
| Aman | 39 | Mumbai | |
| Neha | 44 | 67 | Delhi |
| Krish | 29 | 51 | Pune |
| Simran | Mumbai |
We’ll now apply different COUNT functions on this dataset.
Step 2: COUNT – Count Numbers Only
Use Case: Count how many students have numeric marks in Subject 1.
📍 Formula:
=COUNT(B2:B7)
🧠 This counts only numeric entries in cells B2 to B7 (Subject 1).
It ignores blanks and text.
Step 3: COUNTA – Count Non-Empty Cells
Use Case: Count how many students have any data in Subject 1.
📍 Formula:
=COUNTA(B2:B7)
🧠 This counts all non-empty cells, even if the value is text.
Step 4: COUNTIF – Count With One Condition
Use Case 1: Count students from Mumbai.
📍 Formula:
=COUNTIF(D2:D7, "Mumbai")
🧠 This looks in the "City" column (D) and counts how many cells equal "Mumbai".
Use Case 2: Count students who scored more than 50 in Subject 2.
📍 Formula:
=COUNTIF(C2:C7, ">50")
Step 5: COUNTIFS – Count With Multiple Conditions
Use Case: Count how many students are from Mumbai and scored more than 50 in Subject 2.
📍 Formula:
=COUNTIFS(D2:D7, "Mumbai", C2:C7, ">50")
🧠 This checks two conditions:
-
City is Mumbai
-
Subject 2 marks > 50
Only rows meeting both will be counted.
Mini Project: Data Audit Dashboard
Create a small dashboard at the side of the sheet:
| Metric | Formula |
|---|---|
| Total Students (Names) | =COUNTA(A2:A7) |
| Marks Entered for Subject 1 | =COUNT(B2:B7) |
| Blanks in Subject 1 | =COUNTA(A2:A7)-COUNT(B2:B7) |
| Students from Delhi | =COUNTIF(D2:D7, "Delhi") |
| Delhi Students with Sub 2 > 60 | =COUNTIFS(D2:D7, "Delhi", C2:C7, ">60") |
