Two Way Summary with SUMIFS in Excel

Two-way summary with SUMIFS means using the SUMIFS function to total values that match two or more conditions, such as summing sales for a specific product in a specific month. You enter the sum range first, then pairs of criteria ranges and criteria, and Excel adds only the cells that meet every condition.

Formula

The SUMIFS function adds cells that meet multiple criteria. Its syntax is:

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

• sum_range – the cells to add (e.g., sales amounts).
• criteria_range1 – the first range to check (e.g., product names).
• criteria1 – the condition for that range (e.g., “Apples”).
• criteria_range2, criteria2 – additional pairs for more conditions (e.g., month = “Jan”).

All criteria must be true for a cell to be included in the sum. The ranges must be the same size, but they do not need to be adjacent.

Worked Example

Suppose you have sales data with columns: Product, Month, and Amount. You want total sales of “Apples” in “Jan”.

Data:

• Apples, Jan, 100
• Bananas, Jan, 50
• Apples, Feb, 75
• Apples, Jan, 25

Formula: =SUMIFS(C2:C5, A2:A5, "Apples", B2:B5, "Jan")

Step by step:

1. Check each row: is Product = “Apples”? Yes for rows 1, 3, 4.
2. Among those, check Month = “Jan”. Row 1 (Jan) yes, row 3 (Feb) no, row 4 (Jan) yes.
3. Add the Amount for rows 1 and 4: 100 + 25 = 125.

Result: 125.

How to Build a Two-Way Summary Table

A two-way summary table shows totals for combinations of two variables, like product by month or region by quarter. You can create one manually with SUMIFS in each cell, or use a PivotTable for automatic grouping.

To build manually:

1. Set up row labels (e.g., products) and column labels (e.g., months).
2. In each intersection cell, enter a SUMIFS formula that references the row label and column label as criteria.
3. Use absolute references (with $) for the criteria ranges so you can copy the formula.

Example: In cell B2 (row = Apples, column = Jan), formula: =SUMIFS($C$2:$C$100, $A$2:$A$100, $A2, $B$2:$B$100, B$1). Copy across and down.

Using Cell References and Wildcards

Instead of typing criteria directly, you can reference a cell containing the value. This makes the formula dynamic – change the cell and the sum updates. For text criteria, you can use wildcards:

• * – matches any sequence of characters (e.g., “A*” for any text starting with A).
• ? – matches any single character (e.g., “A?” for two-letter text starting with A).
• ~ – escapes wildcards to match literal * or ?.

For numeric criteria, you can use comparison operators: ">10", "<="&B1, or "<>0".

Common Mistakes and How to Fix Them

• Wrong range sizes: All ranges must be the same number of rows and columns. Mismatched ranges cause #VALUE! errors.
• Criteria as text vs number: If your criteria are numbers stored as text, use quotes or convert. For dates, use DATE() or a cell reference.
• Using SUMIF instead of SUMIFS: SUMIF has a different argument order (sum_range is last). Mixing them up gives wrong results.
• Partial matches: Without wildcards, criteria match exact text (case-insensitive). If you want contains, use "*text*" .
• Hidden rows: SUMIFS ignores hidden rows only if you use SUBTOTAL or AGGREGATE; SUMIFS includes them.

Alternatives to SUMIFS

For more complex two-way summaries, consider:

• PivotTable: Drag fields to rows and columns, values to sum. Fast and automatic, no formulas.
• SUMPRODUCT: Can handle multiple conditions and even array operations, but is slower on large data.
• FILTER + SUM (Excel 365): =SUM(FILTER(amounts, (products="Apples")*(months="Jan"))) – more flexible but requires dynamic array support.
• Power Query: For large data, group and aggregate before loading to Excel.

Frequently Asked Questions

Can SUMIFS sum based on two columns with OR logic?

SUMIFS uses AND logic – all criteria must match. For OR logic (e.g., Apples OR Bananas), you can add two SUMIFS together: =SUMIFS(amounts, products, "Apples") + SUMIFS(amounts, products, "Bananas").

Why is my SUMIFS returning 0?

Check that criteria match exactly (no extra spaces, wrong case is fine, but text must match). Also ensure the sum range is numeric and not formatted as text. Use the formula evaluator to step through.

Can I use SUMIFS across multiple sheets?

Yes, but each criteria range must be on the same sheet. To sum across sheets, use 3D references or SUMPRODUCT with INDIRECT, but that gets complex.

How do I sum with dates between two dates?

Use two criteria pairs on the same date range: =SUMIFS(amounts, dates, ">="&start_date, dates, "<="&end_date).

Does SUMIFS work with whole-column references?

Yes, but it may slow down performance. Prefer specific ranges like A2:A1000.

Related

• OKC to KOA Flights: Routes, Stops, and Tips
• Fuel Contamination: Soil, Groundwater & Well Risks
• All Materials in Bizarre Lineage? Truth & Lore

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2