SUMIFS Formula Generator — Sum with Multiple Conditions | SheetMaster

Master SUMIFS: Sum with Multiple Conditions

The SUMIFS function is an enhanced version of SUMIF that allows you to apply multiple criteria. It is essential for financial analysis, sales commission reports, and complex data aggregation.

Step-by-Step: Using SUMIFS for the First Time

Imagine a sales table:

• Column A: Region (East, West, North)
• Column B: Product Category (Electronics, Furniture, Clothing)
• Column C: Sales Amount

Goal: Sum all sales in the "East" region for "Electronics" products.

How it works: SUMIFS scans column A for "East" AND column B for "Electronics". When a row matches BOTH conditions, it adds column C to the total.

SUMIF vs SUMIFS: Which should I use?

Many users struggle to choose between SUMIF and SUMIFS. Our recommendation is simple: Learn and always use SUMIFS. Why? SUMIFS can do everything SUMIF can (even just one condition), but its formula structure is entirely different and often easier to read because the result column comes first.

Real-World Examples

• HR & Payroll: Sum total hours worked by a specific employee (Condition 1) between January 1 and January 31 (Conditions 2 & 3).
• E-commerce: Sum total revenue for "Electronics" (Condition 1) where order status is "Delivered" (Condition 2) and discount was ">0" (Condition 3).
• Finance: Sum all invoices over $5,000 in Q1 2026 that are still "Unpaid".
• Marketing: Sum total ad spend for campaign "Summer Sale" where ROI > 200%.
• Inventory: Sum stock quantities for "Electronics" items where reorder flag is "Yes".

SUMIFS with Date Ranges

Sum values between two dates by using two conditions on the same date column:

=SUMIFS(C:C, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,3,31))

For dynamic date ranges from cell references:

=SUMIFS(C:C, A:A, ">="&D1, A:A, "<="&E1)

This sums column C for all rows where column A dates fall within the range. Using cell references makes the formula reusable — just update the dates in D1 and E1.

Advanced: Same-Column Multi-Criteria

To sum values that fall within a numeric range, apply two criteria to the same column:

=SUMIFS(C:C, A:A, ">100", A:A, "<500")

This sums column C where column A is between 100 and 500 (exclusive). The same pattern works for dates, percentages, or any numeric criteria.

Common Mistakes and How to Fix Them

• Wrong argument order: SUMIFS syntax is =SUMIFS(sum_range, criteria_range1, criteria1). The sum range comes FIRST — unlike SUMIF where it comes last. This is the #1 mistake.
• Criteria not quoted: Text criteria need quotes: "=Sales". For cell references: ">"&A1.
• Mismatched range sizes: All ranges must have the same number of rows. A1:A100 + B1:B99 = wrong results.
• SUMIFS returns 0: Filter your data manually to verify rows matching all criteria exist. Check for hidden spaces with TRIM().