Total each category with SUMIFS

Sample data. Expenses in A:B, the formula in E2 is copied down for each category.

The formula

List your categories in a column (D here), click the first Total cell and type:

=SUMIFS(B:B,A:A,D2)

E2 shows 135: 60 + 45 + 30, every Fuel row. Copy it down and Office shows 37.50 and Meals 18.75.

• B:B is the column to add up, the amounts.
• A:A is the column to check.
• D2 is what to match, the category.

Quick check: the three totals add up to 191.25, the same as =SUM(B2:B7). If your category totals come up short, a row has a category that is spelled differently.

Add more rules

Each extra rule is a pair: a column, then its condition. A row is added only when every rule is true.

=SUMIFS(B:B,A:A,"Fuel",B:B,">50")

Only the Fuel rows over 50 count, so the result is 60. Conditions with an operator go in double quotes.

Take the limit from a cell

Put the number in a cell (H1 = 50) and join it to the operator with &:

=SUMIFS(B:B,A:A,D2,B:B,">"&H1)

Same result, 60, and you change the limit without touching the formula.

Between two amounts

Use the same column twice, once for each end:

=SUMIFS(B:B,B:B,">=20",B:B,"<=50")

That adds 25.50, 45 and 30: 100.5.

Everything except one category

"<>" means "not equal to":

=SUMIFS(B:B,A:A,"<>Fuel")

Office and Meals together: 56.25.

SUMIF or SUMIFS?

SUMIF takes one condition and puts the column to add last:

=SUMIF(A:A,D2,B:B)

It gives the same 135. SUMIFS puts the column to add first and takes as many conditions as you need, so it is the one to learn. Both work in every Excel since 2007 and in Google Sheets.

Watch it in 35 seconds

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.