3.3 COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS
Quick answer: the short, self-contained version of this lesson is the guide Excel COUNTIF and COUNTIFS Explained.
COUNTIF and COUNTIFS with example — in 30 seconds
What: COUNTIF counts rows that match one condition. COUNTIFS counts rows that match all conditions.
Why: "How many orders?" is the first question in every review meeting, and it always comes with a condition.
Tiny example: Pune has 4 orders. 3 of them are Delivered. =COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")
COUNTIF and COUNTIFS with example — 30 सेकंदात
काय: COUNTIF एका condition ला जुळणाऱ्या rows मोजतो. COUNTIFS सगळ्या conditions जुळणाऱ्या rows मोजतो.
का: "किती orders?" हा प्रत्येक review meeting मधला पहिला प्रश्न असतो, आणि त्याला नेहमी condition असते.
छोटं उदाहरण: Pune च्या 4 orders आहेत. त्यातल्या 3 Delivered आहेत. =COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")
COUNTIF and COUNTIFS with example — 30 सेकंड में
क्या: COUNTIF एक condition से मेल खाने वाली rows गिनता है. COUNTIFS सारी conditions से मेल खाने वाली rows गिनता है.
क्यों: "कितने orders?" हर review meeting का पहला सवाल होता है, और उसके साथ हमेशा कोई condition होती है.
छोटा उदाहरण: Pune के 4 orders हैं. उनमें से 3 Delivered हैं. =COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")
First look at the data (before → after):
Expected result — Counts
| Formula | Answer |
|---|---|
| =COUNTIF(C2:C11,"Pune") | 4 |
| =COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered") | 3 |
| =COUNTIF(H2:H11,">12") | 5 |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on count counta countblank countif and countifs.
Before table and formula as text
Before — Columns used from the mini dataset
| C: City | H: Mins | I: Status |
|---|---|---|
| Pune | 9 | Delivered |
| Nashik | 12 | Delivered |
| Pune | 14 | Delivered |
| Nagpur | 11 | Cancelled |
| Kolhapur | 16 | Delivered |
| Pune | 8 | Delivered |
| Solapur | 18 | Delivered |
| Sambhaji Nagar | 10 | Returned |
| Pune | 13 | Cancelled |
| Nashik | 17 | Delivered |
About this data: Demo table only — not real customer or company data. Built to practise count counta countblank countif and countifs.
Formula / click path
=COUNTIF(C2:C11,"Pune")
=COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")
=COUNTIF(H2:H11,">12")
Do it step by step:
- Click an empty cell.
- Type
=COUNTIF(C2:C11,"Pune")and press Enter. - Now type
=COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered"). - For "more than 12 minutes", put the operator inside quotes:
=COUNTIF(H2:H11,">12"). - Check your three answers: 4, 3 and 5.
स्टेप बाय स्टेप करून बघा:
- एका रिकाम्या cell वर click करा.
=COUNTIF(C2:C11,"Pune")type करा आणि Enter दाबा.- आता
=COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")type करा. - "12 मिनिटांपेक्षा जास्त" साठी operator quotes मध्ये टाका:
=COUNTIF(H2:H11,">12"). - तुमची तीन उत्तरं check करा: 4, 3 आणि 5.
स्टेप बाय स्टेप करके देखें:
- किसी खाली cell पर click करें.
=COUNTIF(C2:C11,"Pune")type करें और Enter दबाएँ.- अब
=COUNTIFS(C2:C11,"Pune",I2:I11,"Delivered")type करें. - "12 मिनट से ज़्यादा" के लिए operator को quotes में डालें:
=COUNTIF(H2:H11,">12"). - अपने तीन जवाब check करें: 4, 3 और 5.
| Function | Counts | Example | Result |
|---|---|---|---|
| COUNT | Cells with numbers | =COUNT(G2:G11) |
10 |
| COUNTA | Non-empty cells (any type) | =COUNTA(A2:A11) |
10 |
| COUNTBLANK | Empty cells | =COUNTBLANK(A2:I11) |
0 |
| COUNTIF | Cells meeting one condition | =COUNTIF(C2:C11,"Pune") |
4 |
| COUNTIFS | Rows meeting all conditions | =COUNTIFS(D2:D11,"FreshGo",I2:I11,"Delivered") |
5 |
About this data: Made-up rows so you can copy the pattern for count counta countblank countif and countifs. Currencies may use ₹, £, € or $.
More examples: late deliveries =COUNTIF(H2:H11,">15") → 3; orders in any city starting with "Na" (Nashik, Nagpur) =COUNTIF(C2:C11,"Na*") → 3.
Steps in Excel – delivery performance table
- K10
Delivered:=COUNTIF(I2:I11,"Delivered")→ 7. - K11
Cancelled:=COUNTIF(I2:I11,"Cancelled")→ 2. - K12
Cancellation %:=K11/COUNTA(A2:A11)→ 20%.
Ravindra Bagale's Tip
COUNT counts only numbers – many students use COUNT on Order ID (text) and get 0. Use COUNTA for text. Also, COUNTA treats a cell that "looks empty" but contains a space as filled, so sometimes the count comes out higher – clean the data first (Module 5).
Ravindra Bagale's Tip – मराठी
COUNT फक्त numbers मोजतो – बरेच students Order ID (text) वर COUNT लावतात आणि 0 येतो. Text साठी COUNTA वापरा. आणि COUNTA ला space असलेली "रिकामी दिसणारी" cell पण भरलेली वाटते, म्हणून कधी कधी count जास्त येतो – आधी data clean करा (Module 5).
Ravindra Bagale's Tip – हिंदी
COUNT सिर्फ़ numbers गिनता है – बहुत से students Order ID (text) पर COUNT लगाते हैं और 0 आता है. Text के लिए COUNTA इस्तेमाल करो. और COUNTA को space वाली "खाली दिखने वाली" cell भी भरी हुई लगती है, इसलिए कभी-कभी count ज़्यादा आता है – पहले data clean करो (Module 5).
Practice task
Count: orders per platform, orders in Pune that took ≤ 10 minutes, and blank cells in the Status column of the full Orders sheet.
Loading practice tasks...
- How many amounts? (Easy): Count how many numbers are in the Amount column.
- Orders from Pune (Easy): Count how many orders have City "Pune".
- Clinic: how many ages? (Easy): On the Clinic sheet, count how many numbers are in the Age column.
- Clinic: heart patients (Easy): On the Clinic sheet, count how many patients have Department "Cardiology".
- Orders without a note (Medium): Count the orders where the Note column is empty.
- Slow deliveries (Medium): Count the orders where Mins is more than 12.
- Clinic: no follow-up (Medium): On the Clinic sheet, count the patients where the Follow-up note column is empty.
- Pune and Delivered (Hard): Count the orders where City is "Pune" and Status is "Delivered".
- Notes written (Hard): Count how many orders have something written in the Note column.
- Clinic: senior heart patients (Hard): On the Clinic sheet, count the patients where Department is "Cardiology" and Age is more than 60.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
