Ravindra BagaleCourses & study guides Track your progress

Guides

How to Use SUMIF and SUMIFS in Excel

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

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

SUMIF totals amounts that match one condition. SUMIFS totals amounts that match several conditions at once.

Chala mitrano! “Mumbai cha total kitna?” — SUMIF. “Mumbai + Online?” — SUMIFS. Why? Conditional totals without Pivot every time. How? Tiny sales table, hand-check the math. Stay with me.

Quick answer

Vertical steps:

  1. Keep City, Channel, Amount columns.
  2. One city: =SUMIF(CityRange,"Mumbai",AmountRange).
  3. Hand-add Mumbai rows to verify.
  4. Two rules: =SUMIFS(AmountRange,CityRange,"Mumbai",ChannelRange,"Online").
  5. Remember: SUMIFS puts sum range first.
  6. Point criteria at cells so you can change city easily.
=SUMIF(A2:A6,"Mumbai",C2:C6)
=SUMIFS(C2:C6,A2:A6,"Mumbai",B2:B6,"Online")

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

Before and after (look at the tables first)

Before — all rows (total 190)

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

After — SUMIF City = Mumbai → 150

City Amount kept
Mumbai 100
Mumbai 50
Sum 150
Before SUMIF All sales rows before conditional sum.

Before

After SUMIF Mumbai rows total 150.

After

Why SUMIF / SUMIFS?

  1. PivotTable is great; sometimes one cell formula is enough.
  2. Dashboards and phone-bill trackers use conditional totals everywhere.
  3. SUMIFS answers “this city AND this channel”.

What happens — SUMIF

=SUMIF(A2:A4,"Mumbai",C2:C4)
  1. Excel tests each City.
  2. Keeps Mumbai rows (100 and 50).
  3. Drops Pune 40.
  4. Sums → 150.

What happens — SUMIFS

=SUMIFS(C2:C4,A2:A4,"Mumbai",B2:B4,"Online")
  1. City must be Mumbai and Channel Online.
  2. Only 100 remains.
  3. Result → 100.

Vertical practice steps

  1. Type the three-row table.
  2. Write SUMIF for Mumbai → expect 150.
  3. Write SUMIFS Mumbai+Online → expect 100.
  4. Write SUMIFS Mumbai+Store → expect 50.
  5. Put "Mumbai" in G1; rewrite SUMIF to use G1.
  6. Change G1 to Pune → expect 40.

Argument order (do not mix)

Function Order
SUMIF criteria range → criteria → [sum range]
SUMIFS sum range first → range/criteria pairs

Ravindra Bagale's Tip

Zero result? Spelling first — "Mumbai " with a trailing space is not Mumbai. TRIM the City column in cleaning week. Formula synata theek asel, data ghan asel.

Practice task

  1. Add Nashik Store 80 to the sheet.
  2. SUMIF Nashik → 80.
  3. SUMIFS Online only (all cities) → 100+40=140.
  4. Compare one answer with a PivotTable.

Learn it properly

Course lesson:

Related guides: COUNTIF and COUNTIFS · Create PivotTable · IF function

Samajla ka? SUMIF = one filter total. SUMIFS = many filters total. Hand-check tiny numbers once. Aata pudhe COUNTIF.

Frequently asked questions

SUMIF vs SUMIFS?

SUMIF handles one condition. SUMIFS handles several conditions at once (all must match).

Argument order difference?

SUMIF: range, criteria, [sum_range]. SUMIFS: sum_range first, then range/criteria pairs.

Can criteria be a cell?

Yes. Use SUMIF(A:A,G1,C:C) so G1 holds "Mumbai".

Text vs numbers?

City names need quotes in the formula ("Mumbai"). Numbers can be bare or in quotes.

Why zero result?

Spelling mismatch, extra spaces, or wrong sum range. Check one matching row manually.

Course lesson?

Chapter 3 — SUM, SUMIF and SUMIFS.