SUMIFS vs Pivot Table: Which Should You Use?

"Total revenue by region" is the most common question asked of any spreadsheet, and Excel gives you two very different ways to answer it. SUMIFS is a formula: it lives in a cell, recalculates when the data changes, and answers exactly one question per cell. A pivot table is a report: it summarizes an entire table by any combination of fields, but it's a snapshot that you refresh.

People who learned one tend to use it for everything. This guide shows the same data worked both ways so you can pick the right tool for the job — and shows the shortcut when you'd rather just ask.

The data

A small sales sheet, columns A–E:

SUMIFS: one question, one cell

SUMIFS sums a range wherever all of its criteria are true. The sum range comes first, then criteria pairs:

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

Revenue for the North region:

=SUMIFS(D:D, A:A, "North")

→ 6,900.50

Revenue for Widgets sold in the North:

=SUMIFS(D:D, A:A, "North", B:B, "Widget")

→ 2,400.50

Revenue in February — dates need two criteria, a lower and an upper bound:

=SUMIFS(D:D, E:E, ">="&DATE(2024,2,1), E:E, "<"&DATE(2024,3,1))

→ 10,500

The ">="& construction trips up almost everyone the first time. The criterion is a text string that starts with an operator; & glues the operator to the date. Writing >=DATE(2024,2,1) without the quotes and ampersand is a syntax error.

Make it reusable. Put the region in cell G1 and reference it:

=SUMIFS(D:D, A:A, G1)

Now G1 is a control: change it to "East" and the total updates. Fill a column of region names next to a column of these formulas and you've built a small report by hand.

The SUMIFS family

The same criteria syntax works across four functions:

=COUNTIFS(A:A, "East") → 2 sales. =AVERAGEIFS(D:D, B:B, "Widget") → 2,666.83.

Where SUMIFS falls down

• Many groupings. Revenue by region and product and month is 4 × 3 × 3 = 36 cells, each a formula. Doable; miserable to maintain.
• You don't know the categories yet. SUMIFS needs you to type "North". If a new region appears next month, nothing tells you.
• Whole-column references on big sheets (D:D) recalculate slowly past ~100k rows. Use a Table or a bounded range.

Pivot table: every question, one report

Select the data, Insert → PivotTable, then drag fields:

• Region → Rows
• Revenue → Values (defaults to Sum)

You get all four regions and their totals in one step, including any region you didn't know existed. Add Product → Columns and you have the 4 × 3 grid that would have been 12 SUMIFS formulas. Drag Date → Rows and Excel groups it by month automatically.

Pivots also do things formulas do badly:

• Show values as % of column total — right-click a value → Show Values As.
• Distinct counts — add the data to the Data Model when creating the pivot, then choose Distinct Count.
• Rank and running total — also under Show Values As.

Where pivot tables fall down

• They don't update automatically. Add rows to the source and the pivot shows the old totals until you right-click → Refresh. If the source range wasn't a Table, new rows may fall outside it entirely — the most common pivot bug there is.
• You can't reference a pivot cell reliably. GETPIVOTDATA exists, but layouts move when fields change.
• They're hard to audit. A number in a pivot can't be traced with Evaluate Formula.

Decision guide

A common pattern in well-built workbooks: a pivot to explore, then a handful of SUMIFS to publish the numbers that matter on a summary sheet.

The shortcut: ask

Both approaches require you to know what you want before you start. If you'd rather find out, upload the sheet to ChatExcel and ask: "total revenue by region and product" returns the grid; "which region grew most from January to March?" returns an answer no pivot layout gives you directly. Ask "how do I do that in Excel?" and you get the SUMIFS above, written against your real column letters — useful when the answer needs to live in the workbook permanently.

Common mistakes

1. Criteria on numbers stored as text. If Revenue is text (left-aligned, or a green triangle), SUMIFS returns 0. Convert with Text to Columns or VALUE().
2. Mismatched range sizes. SUMIFS(D2:D100, A2:A50, …) errors with #VALUE!. All ranges must be the same height.
3. Trailing spaces in categories. "North " ≠ "North". Wrap the criteria range with TRIM in a helper column, or use wildcard criteria: "North*".
4. Pivot not refreshed. Always refresh after pasting new rows, and make the source a Table (Ctrl+T) so it grows automatically.