SUMIFS: turn a raw QuickBooks export into a P&L with one formula

Export your general ledger from QuickBooks (or Xero, or a bank feed) and what lands in Excel isn't a report — it's a flat list. One row per transaction: a date, an account, an amount, maybe a class or a vendor. Hundreds or thousands of rows. Somewhere in there is your P&L, but it's smeared across every line.

The one formula that turns that pile into a statement is SUMIFS— “sum with conditions.” It adds up only the rows that match the rules you give it, so you can say add every amount where the account is Revenue and the month is Juneand get one number back. Do that for each line and each period and you've built a P&L that rebuilds itself the moment you paste in fresh data.

The problem: a ledger is a list, not a report

Here's what an export actually looks like — a transaction log, sorted by date, with the account and amount you care about buried in columns you now have to add up by hand:

You could filter to Revenue, eyeball June, and type the total into a summary tab. That works exactly once. Next month the rows change and your hand-typed number is a lie. SUMIFS does the filtering and the adding, live, so the summary is never stale.

SUMIFS, one argument at a time

The syntax reads a little backwards the first time, because the thing you want (the numbers to add) comes first, and the conditions come after in pairs:

• sum_range — the column of numbers to total. Here, Amount.
• criteria_range1, criteria1 — a column to test and what it has to equal. Account must be "Revenue".
• criteria_range2, criteria2, … — repeat for every extra condition. Month must be "Jun". You can stack up to 127 pairs; a row has to pass all of them to be counted.

Point the criteria at a cell instead of a typed value and the formula becomes a control panel: put the month in B1, reference $B$1, and the whole statement re-reads when you change one cell.

Two conditions: the actual P&L cell

A real P&L cell is the intersection of a line (Revenue) and a period(June) — so it takes two conditions. This is the formula you'll copy across a whole grid of accounts and months:

Anchor the ranges with $ (so $D:$D and $B:$Bdon't shift as you fill), leave the account and month references relative so they pick up the row and column headers, and one formula fills the whole matrix. That's the entire trick: a wall of the same SUMIFS, each cell reading its own row label and column label.

The finished statement, built from the list

Fill that formula down the lines and across the months and the flat export becomes a statement that foots and updates itself:

The subtotal rows — Gross profit, Net income — are plain arithmetic on the SUMIFS cells above them, not more lookups. That's the whole model: SUMIFS pulls the raw lines up to each P&L line, and simple addition stacks them into subtotals.

The traps that return a wrong number silently

SUMIFS almost never throws an error when it's wrong — it just returns 0, or a number that's a little off, and looks perfectly confident doing it. These are the four that catch people:

• Text vs. number mismatch.If your account codes came in as text (“4100” the label, not 4100the number), a numeric criterion won't match and you get 0. Left-aligned values in a column of numbers are the tell.
• Trailing spaces. "Revenue " with a stray space is not "Revenue". Exports are full of them. Wrap the source column in TRIM once, or clean it on import.
• Mismatched range sizes. If sum_range is D2:D500 but a criteria range is B2:B499, you get a #VALUE!. Keep every range the exact same height — whole-column references (D:D, B:B) sidestep it entirely.
• Operators need quotes. To sum amounts over 30 days or above a threshold, the operator goes inside the quotes: ">30", or ">="&B1 when the cutoff lives in a cell.

SUMIFS or a PivotTable?

The honest answer: both, for different jobs. A PivotTable is faster when you're exploring— drag account onto rows, month onto columns, and you've got a cross-tab in ten seconds, no formulas. But it's a snapshot: it doesn't recalculate until you remember to hit Refresh, and you can't drop one cell into a sentence or a chart without it fighting you.

SUMIFS wins when the layout is fixedand needs to stay live — a P&L that always has the same lines in the same order, feeding a dashboard, a forecast, or a board pack. Every cell recalculates the instant the data changes, and each one is a normal cell you can reference anywhere. Reach for the Pivot to find the shape of the data; reach for SUMIFS to build the report you'll reuse every month.

Its two siblings round out the set: COUNTIFS counts the rows that match instead of summing them (how many invoices are 30+ days late), and AVERAGEIFS averages them (average deal size by stage). Same argument shape, different verb.