SUMIF and SUMIFS in Excel

Watch the full video tutorial here: https://youtu.be/8jcGB_w00_M

If you want to learn sumif and sumifs in Excel, this step-by-step guide covers everything from the basic syntax to real examples, so you can start using these formulas in your own spreadsheets today. Both SUMIF and SUMIFS in Excel are used to add up numbers based on one or more conditions, making them essential for anyone working with sales data, expense reports, or any dataset that needs conditional totals. Watch the full video above for a live, practical walkthrough alongside this guide.

What is SUMIF in Excel?

SUMIF is a formula that adds up values in a range based on a single condition. Instead of manually filtering and adding numbers, SUMIF does it automatically.

Syntax: =SUMIF(range, criteria, [sum_range])

• range – the cells you want to check against your condition
• criteria – the condition that must be met
• sum_range – the actual cells to add up (optional if it’s the same as range)

Example: =SUMIF(A2:A100, “Delhi”, B2:B100) adds up all values in column B where column A says “Delhi”.

What is SUMIFS in Excel?

SUMIFS is an extension of SUMIF that lets you apply multiple conditions at once, across different columns.

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• sum_range – the cells to add up
• criteria_range1, criteria1 – the first condition pair
• criteria_range2, criteria2 – an optional second condition pair (you can add many more)

Example: =SUMIFS(C2:C100, A2:A100, “Delhi”, B2:B100, “January”) adds up column C only where column A is “Delhi” AND column B is “January”.

Difference Between SUMIF and SUMIFS in Excel

• SUMIF supports only one condition; SUMIFS supports multiple conditions
• In SUMIF, sum_range comes last; in SUMIFS, sum_range comes first
• Use SUMIF for simple, single-criteria totals
• Use SUMIFS when you need to filter by two or more criteria at the same time

Why Use SUMIF and SUMIFS in Excel?

• Calculate conditional totals without manual filtering
• Build dynamic reports that update automatically as data changes
• Combine multiple conditions (region, date, product, category) in one formula
• Save time compared to using filters and manual addition
• Reduce errors from manually selecting ranges

Common Uses of SUMIF and SUMIFS in Excel

• Total sales by region, product, or salesperson
• Monthly or yearly expense tracking by category
• Attendance or marks totals filtered by class or subject
• Budget tracking with multiple filter conditions
• Inventory totals filtered by warehouse or product type

Common Mistakes to Avoid with SUMIF and SUMIFS

• Mismatched range sizes (range and sum_range must be the same size)
• Forgetting quotation marks around text criteria (e.g., “Delhi”, not Delhi)
• Using SUMIF when multiple conditions are actually needed (use SUMIFS instead)
• Not locking ranges with $ signs when copying the formula across cells
• Confusing the argument order between SUMIF and SUMIFS

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS in Excel? SUMIF handles a single condition, while SUMIFS handles multiple conditions across different columns. SUMIFS is essentially SUMIF with the ability to add more criteria pairs.

Can SUMIFS have more than two conditions? Yes. SUMIFS supports up to 127 range/criteria pairs, so you can filter by many conditions at once, such as region, date, and product category together.

Can I use SUMIF or SUMIFS with dates? Yes. You can use comparison operators like “>=” and “<=” with dates as criteria, for example =SUMIFS(C2:C100, B2:B100, “>=”&DATE(2026,1,1)) to total values from a certain date onward.

Why is my SUMIF formula returning zero? This usually happens due to mismatched data types (text vs. numbers), extra spaces in criteria, or a range/sum_range mismatch in size. Double-check that your criteria text matches exactly what’s in the data.

Watch the Full Tutorial

For a complete step-by-step walkthrough with live examples, watch the full video above on our channel CA Gyaan Sadhna. Don’t forget to subscribe for more Excel tutorials!