Ravindra BagaleCourses & study guides Track your progress

Guides

Excel मध्ये COUNTIF आणि COUNTIFS कसे वापरायचे

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

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

COUNTIF एक condition जुळणाऱ्या rows count करतं. COUNTIFS अनेक conditions जुळणाऱ्या rows count करतं.

मित्रांनो, एकूण रुपये नको — “किती orders?” हवेत. COUNTIF count करतं. Staffing आणि stock प्रश्नांना row counts लागतात. SUMIF सारखीच shop sheet.

थोडक्यात उत्तर

Vertical steps:

  1. City / Channel columns वापरा.
  2. =COUNTIF(CityRange,"Mumbai") → Mumbai rows किती.
  3. =COUNTIFS(CityRange,"Mumbai",ChannelRange,"Online") → दोन्ही rules.
  4. AutoFilter count शी तुलना.
  5. Amount वर ">=100" — high-value lines.
  6. प्रत्येक criteria range same height ठेवा.
=COUNTIF(A2:A4,"Mumbai")
=COUNTIFS(A2:A4,"Mumbai",B2:B4,"Online")

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

Before आणि after

Before — तीन orders

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

After — COUNTIF Mumbai → 2 (150 रुपये नाही — दोन 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

COUNTIF / COUNTIFS का?

  1. SUMIF उत्तर: “किती पैसे”.
  2. COUNTIF उत्तर: “किती lines / orders / customers”.
  3. Interviews मध्ये दोन्ही बाजूला येतात.

काय होतं

  1. COUNTIF Mumbai → rows 1 आणि 2 → 2.
  2. COUNTIFS Mumbai + Online → फक्त row 1 → 1.
  3. COUNTIF Amount ">=100" → फक्त 100 → 1.

Vertical practice steps

  1. तीन-row table पुन्हा बनवा.
  2. COUNTIF City Mumbai → 2.
  3. COUNTIF City Pune → 1.
  4. COUNTIFS Mumbai Online → 1.
  5. AutoFilter City = Mumbai; 2 visible rows confirm.
  6. चौथी Mumbai Online row; recount → 3 आणि 2.

COUNTIF vs SUMIF

Need Function
Matching rows किती COUNTIF / COUNTIFS
Matching Amount total SUMIF / SUMIFS

Ravindra Bagale's Tip

विद्यार्थी COUNTIF बघून रुपये अपेक्षित करतात. Count = rows. Sum = money. Week one sticky note. Simple — powerful.

Practice task

  1. दहा rows shop sales (mix cities).
  2. प्रत्येक city COUNTIF.
  3. City + channel COUNTIFS.
  4. Amount वर एक COUNTIF ">=100".

Learn it properly

Related guides: SUMIF/SUMIFS · FILTER · IF

समजलं का? COUNTIF एक rule ने rows count; COUNTIFS अनेक. Money sum नव्हे. आता 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.