SUMIFS Multiple Criteria in Excel (Complete Guide With Examples)

Excel is one of the most widely used tools in accounting and financial analysis. One of the most powerful functions accountants rely on is SUMIFS, which allows you to sum numbers based on multiple conditions.

The SUMIFS function is commonly used for:

• Financial reporting

• Budget analysis

• Commission calculations

• Sales reporting

• Expense tracking

In this guide you will learn:

• What the SUMIFS function is

• How to use SUMIFS with multiple criteria

• Practical accounting examples

• Common mistakes to avoid

• A faster way to generate formulas using AI

What Is the SUMIFS Function?

The SUMIFS function adds numbers in a range that meet multiple criteria.

Basic Syntax

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Explanation

ArgumentDescriptionsum_rangeThe numbers you want to addcriteria_range1First column to checkcriteria1First conditioncriteria_range2Second column to checkcriteria2Second condition

This function is extremely useful when working with large financial datasets.

Example Data

Assume you have a sales dataset.

SalespersonRegionProductRevenueJohnEastSoftware1200SarahWestHardware900JohnEastHardware600DavidWestSoftware1500JohnEastSoftware800

Now we want to calculate:

Total revenue for John in the East region

Example 1: SUMIFS With Two Criteria

Formula:

=SUMIFS(D2:D6,A2:A6,"John",B2:B6,"East")

Explanation

• D2:D6 → revenue to sum

• A2:A6 → salesperson column

• "John" → first condition

• B2:B6 → region column

• "East" → second condition

Result

The formula will return:

2600

Because:

1200 + 600 + 800 = 2600

Example 2: SUMIFS With Product Criteria

Now calculate total Software revenue in the East region.

Formula:

=SUMIFS(D2:D6,B2:B6,"East",C2:C6,"Software")

Result:

2000

Because:

1200 + 800 = 2000

Example 3: SUMIFS With Date Criteria

SUMIFS is also useful for financial reporting by date.

Example dataset:

DateCategoryAmount01-JanMarketing30002-JanSoftware60003-JanMarketing20004-JanSoftware500

Formula to calculate Marketing expenses in January:

=SUMIFS(C2:C5,B2:B5,"Marketing")

Result:

500

Example 4: SUMIFS With Greater Than Condition

You can also use logical operators.

Example:

Calculate revenue greater than $1000

=SUMIFS(D2:D6,D2:D6,">1000")

This will sum:

1200 + 1500 = 2700

Why Accountants Use SUMIFS Frequently

The SUMIFS function is essential in accounting workflows.

Common use cases include:

Budget vs Actual Analysis

Calculate totals for departments or cost centers.

Commission Calculations

Sum sales based on salesperson and region.

Expense Tracking

Filter expenses by category.

Financial Reporting

Create dynamic reports without pivot tables.

Common SUMIFS Mistakes

Using Different Range Sizes

All ranges must be the same size.

Incorrect:

=SUMIFS(D2:D10,A2:A6,"John")

Correct:

=SUMIFS(D2:D6,A2:A6,"John")

Incorrect Criteria Format

Text criteria must be inside quotes.

Incorrect:

=SUMIFS(D2:D6,A2:A6,John)

Correct:

=SUMIFS(D2:D6,A2:A6,"John")

The Problem With Writing Complex Excel Formulas

Although SUMIFS is powerful, writing formulas can become difficult when:

• Data contains many conditions

• Multiple sheets are involved

• Financial models grow large

• Time pressure during month-end close

Accountants often spend hours writing and debugging formulas.

Faster Way: Generate SUMIFS Formulas with AI

Instead of manually writing formulas, you can generate them instantly using AI.

Example prompt:

Sum revenue where salesperson is John and region is East

An AI formula generator can instantly create the correct SUMIFS formula.

Try SheetAI — AI Excel Formula Generator for Accountants

SheetAI helps accountants generate Excel formulas instantly.

Instead of writing complex formulas manually, simply describe the calculation you need.

SheetAI can help you:

• Generate SUMIFS formulas instantly
• Build reconciliation formulas
• Automate financial analysis
• Reduce spreadsheet errors
• Speed up month-end close

Try it here:

https://smartsideai.lovable.app

Example Prompt You Can Try

Paste this into SheetAI:

Sum revenue where salesperson is John and region is East

SheetAI will generate the correct Excel formula instantly.

Best Practices for Using SUMIFS

Keep Data Clean

Ensure there are no extra spaces or inconsistent text values.

Use Structured Tables

Excel tables make formulas easier to maintain.

Combine With Other Functions

SUMIFS works well with:

• IF

• XLOOKUP

• INDEX MATCH

Avoid Hardcoding

Use cell references instead of typing text conditions.

Conclusion

The SUMIFS function is one of the most useful Excel formulas for accountants and finance professionals. It allows you to calculate totals based on multiple conditions, making financial analysis faster and more accurate.

However, writing complex formulas manually can take time and lead to errors.

Tools like SheetAI allow accountants to generate formulas instantly using plain English.

If you want to automate Excel formulas and work faster in spreadsheets, try SheetAI:

https://smartsideai.lovable.app

Generate your first Excel formula in seconds.