Ravindra BagaleCourses & study guides Track your progress

Guides

How to Use COUNTIF and COUNTIFS in Excel

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

COUNTIF counts rows that match one condition. COUNTIFS counts rows that match several conditions.

Chala mitrano! Total rupaye nahi — “kitne orders?” hava aahe. COUNTIF counts. Why? Staffing and stock questions need row counts. How? Same shop sheet as SUMIF. Stay with me.

Quick answer

Vertical steps:

  1. Use City / Channel columns.
  2. =COUNTIF(CityRange,"Mumbai") → how many Mumbai rows.
  3. =COUNTIFS(CityRange,"Mumbai",ChannelRange,"Online") → both rules.
  4. Compare with AutoFilter count.
  5. Try ">=100" on Amount for high-value line count.
  6. Keep every criteria range the same height.
=COUNTIF(A2:A4,"Mumbai")
=COUNTIFS(A2:A4,"Mumbai",B2:B4,"Online")

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Before — three orders

City Channel Amount
Mumbai Online 100
Mumbai Store 50
Pune Online 40

After — COUNTIF Mumbai → 2 (not 150 rupees — two rows)

Metric Result
COUNTIF Mumbai 2
COUNTIFS Mumbai+Online 1
Before COUNTIF Three order rows before counting.

Before

After COUNTIF Counts show 2 and 1.

After

Why COUNTIF / COUNTIFS?

  1. SUMIF answers “how much money”.
  2. COUNTIF answers “how many lines / orders / customers”.
  3. Both appear in interviews side by side.

What happens

  1. COUNTIF Mumbai → rows 1 and 2 → 2.
  2. COUNTIFS Mumbai + Online → only row 1 → 1.
  3. COUNTIF Amount ">=100" → only 100 → 1.

Vertical practice steps

  1. Recreate the three-row table.
  2. COUNTIF City Mumbai → 2.
  3. COUNTIF City Pune → 1.
  4. COUNTIFS Mumbai Online → 1.
  5. AutoFilter City = Mumbai; confirm 2 visible rows.
  6. Add a fourth Mumbai Online row; recount → 3 and 2.

COUNTIF vs SUMIF

Need Function
How many matching rows COUNTIF / COUNTIFS
Total of Amount for matches SUMIF / SUMIFS

Ravindra Bagale's Tip

Students stare at COUNTIF and expect rupees. Count = rows. Sum = money. Write that on a sticky note for week one. Simple — powerful.

Practice task

  1. Ten rows of shop sales (mix cities).
  2. COUNTIF each city.
  3. COUNTIFS city + channel.
  4. One COUNTIF on Amount ">=100".

Learn it properly

Course lesson:

Related guides: SUMIF and SUMIFS · FILTER function · IF function

Samajla ka? COUNTIF counts rows with one rule; COUNTIFS with many. Not the same as summing money. Aata pudhe INDEX+MATCH.

Frequently asked questions

COUNTIF vs COUNTIFS?

COUNTIF = one condition. COUNTIFS = several conditions (AND logic).

Does COUNTIF sum money?

No. It counts rows. Use SUMIF when you need rupee totals.

Count blanks?

COUNTBLANK for empty cells, or COUNTIF(range,"") in some cases.

Wildcards?

Yes. COUNTIF(A:A,"Mum*") can count cities starting with Mum — use carefully.

Zero when you expect a number?

Check spelling, trailing spaces, and that ranges align.

Course lesson?

Chapter 3 — COUNT family including COUNTIF and COUNTIFS.