Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
SUMIFS
AggregationSum amounts that match several conditions — the workhorse of P&L roll-ups.
Difficulty
1What is it?
SUMIFS adds up the cells in a range that meet one or more conditions across other ranges. It's how you turn a flat general ledger into a structured report: revenue for a given month, opex for a given department, and so on.
2What it looks like
SUMIFS(sum_range, criteria_range1, criteria1, …)- sum_range
- The numbers to add (e.g. the Amount column).
- criteria_range1
- The column to test.
- criteria1
- The condition — text ("Revenue"), a number, or an operator in quotes (">30").
3When you use it
- Roll a GL up to a P&L line by account and month.
- Total spend by vendor, department, or class.
- Sum within a range using two criteria — ">=" start and "<=" end.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Total the amounts for one account.
=SUMIFS(B2:B5, A2:A5, "Revenue")$85,000| A | B | C | |
|---|---|---|---|
| 1 | Account | Amount | Result |
| 2 | Revenue | $80,000 | $85,000 |
| 3 | COGS | $32,000 | |
| 4 | Revenue | $5,000 | |
| 5 | Opex | $28,000 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#VALUE!The ranges aren't the same size.Fix: Make sum_range and every criteria_range identical in height.
0 (unexpected)Criteria don't match — usually text/number or trailing-space mismatches.Fix: Confirm the data type matches and TRIM stray spaces; quote operators like ">30".
6Better functions & alternatives
- SUMPRODUCT — Conditional sums in older Excel, or with more exotic logic.
- PivotTable — Faster for exploring many cuts at once without writing formulas.
Want SUMIFS already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →