How to Use SUMIFS to Analyse Data Like a Pro

If you’re still using SUM and IF separately to crunch numbers in Excel, it’s time to level up. SUMIFS is one of Excel’s most powerful functions for analysing data — and it’s surprisingly easy to master. With SUMIFS, you can total up values based on multiple conditions, making your spreadsheets smarter, more dynamic, and far less prone to error.

What Is SUMIFS?

SUMIFS adds numbers together only when they meet all the conditions you specify. It’s like saying: “Add up the sales only for March and only for the South region.” Instead of filtering manually or using pivot tables, SUMIFS does it on the fly.

Basic Syntax

Here’s how the formula works:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

It looks intimidating at first, but once you plug in real data, it becomes second nature.

Example 1: Sales by Region and Product

Imagine you’ve got this dataset:

Region     Product     Sales

South      Apples      120

South      Oranges     90

North      Apples      130

To sum sales where Region is South and Product is Apples:

=SUMIFS(C2:C100, A2:A100, "South", B2:B100, "Apples")

Here’s what each part means:

• C2:C100 → where the numbers you want to total are


• A2:A100 = "South"


• B2:B100 = "Apples"

Using Cell References Instead of Hardcoding

Hardcoding is fine, but dynamic formulas are better. Use cell references like:

=SUMIFS(C2:C100, A2:A100, E1, B2:B100, F1)

This lets users select or type values, and Excel reacts instantly.

Use with Dates, Greater Than, or Ranges

You’re not limited to exact matches. You can use:

=SUMIFS(C2:C100, A2:A100, ">=01/01/2024", A2:A100, "<=31/01/2024")

This adds up only January sales. Great for dashboards and monthly summaries.

Want to see how SUMIFS works inside a live dashboard? Check out our guide on designing better Excel dashboards — it shows how to make your data truly shine.

Common Mistakes to Avoid

• Misaligned Ranges: Your sum_range and each criteria_range must be the same size.


• Forgetting Quotation Marks: Text or operators (like >=) need to be in quotes.


• Using SUMIF for Multiple Conditions: Use SUMIFS, not nested SUMIFs. It’s cleaner and faster.

If your data isn’t clean, SUMIFS might not behave as expected. Before diving into formulas, consider preparing your data with Power Query — it’s a powerful tool for cleaning and transforming Excel data with ease.

Bonus: Named Ranges and Tables

Make your life easier by turning your data into a Table (Ctrl + T). Then use structured references like:

=SUMIFS(Table1[Sales], Table1[Region], "South", Table1[Product], "Apples")

Final Thoughts

SUMIFS is an essential tool in every Excel analyst’s toolbox. Once you get the hang of it, you’ll be able to build dynamic reports, filter complex data in a flash, and stop relying on clunky filters or manual calculations.

Want a sample workbook with ready-to-practice examples? Just get in touch — we’re happy to share it!