Spreadsheets

SUMIF not equal: total all expenses except one category

Total every expense except one category using SUMIF with the not-equal operator, then extend it to rent and EMI together to see everyday spending.

Editorial illustration for SUMIF not equal: total all expenses except one category

To total everything except one category, use the not-equal operator inside the criterion: =SUMIF($B$2:$B$500,"<>Rent",$C$2:$C$500). The "<>" means is not equal to, so every row whose category is anything other than Rent is added. For two or more exclusions, switch to SUMIFS and repeat the condition, or subtract the excluded totals from the grand total.

Why would I total everything except one category?

Fixed commitments such as rent, EMI and school fees swamp a monthly total and hide the spending you can actually change. If rent is ₹22,000 and everything else is ₹48,500, comparing months by the grand total tells you mostly about rent. Taking the fixed items out gives an everyday spending figure that moves when your habits move.

Say your sheet has date in column A, category in column B and amount in column C, with spends entered as positive numbers.

How do I exclude several categories?

SUMIF accepts only one condition, so use SUMIFS, which accepts as many as you like and treats them as AND. Rent AND EMI both need to be excluded:

  • =SUMIFS($C$2:$C$500,$B$2:$B$500,"<>Rent",$B$2:$B$500,"<>EMI")
  • Subtraction version: =SUM($C$2:$C$500)-SUMIF($B$2:$B$500,"Rent",$C$2:$C$500)-SUMIF($B$2:$B$500,"EMI",$C$2:$C$500)

Both give the same answer. The subtraction version is easier to read when you have two exclusions. SUMIFS is better when you have many exclusions, because you can keep them in cells and add one condition at a time.

Worked example with made-up numbers

ItemAmount (₹)
All spending in the month85,000
Rent22,000
EMI14,500
Everyday spending (85,000 − 22,000 − 14,500)48,500

Run both formulas on the same data and each should give ₹48,500. If they differ, some rows have a category the formulas treat differently, which brings us to the next question.

Why does "<>Rent" also add my blank rows?

A row with an empty category is not equal to Rent, so it is included. That may be exactly what you want, because uncategorised spending is still spending. If you want only categorised rows, add a condition for non-blank cells: =SUMIFS($C$2:$C$500,$B$2:$B$500,"<>Rent",$B$2:$B$500,"<>"). The "<>" on its own means not empty.

Can I keep the excluded category in a cell?

Yes. Type Rent in F1 and use =SUMIF($B$2:$B$500,"<>"&F1,$C$2:$C$500). The ampersand joins the operator and the cell value into one criterion. Without it, "<>F1" would look for a category literally called F1. Matching is not case sensitive, so rent and RENT are the same. A trailing space is not forgiven, so "Rent " would be counted as a different category and sneak into your total.

What about partial matches such as all loan categories?

Wildcards work with the not-equal operator. =SUMIF($B$2:$B$500,"<>*Loan*",$C$2:$C$500) excludes any category with the word Loan in it, such as Home Loan EMI and Car Loan EMI. Be careful that the wildcard is not catching a category you did want.

Watch out for credits in the same column

If refunds or salary credits sit in the amount column as negative or positive numbers beside spends, an everyday total can be distorted. Keep credits in a separate column, or add a condition such as amount greater than zero: ,$C$2:$C$500,">0". Check how your own statement shows credits before you decide.

You can also flip the question. Keep a small list of fixed categories, such as Rent, EMI and School fees, in cells H2:H4, and total them with =SUMPRODUCT(SUMIF($B$2:$B$500,H2:H4,$C$2:$C$500)). Subtract that from the grand total to get everyday spending. When a new fixed commitment starts, add its name to the list and every total updates, with no formula edits.

When a sheet stops being enough

SpendixAI lets you create custom categories and sub-categories, and its Report builder filters and groups spending, so you can look at everything except fixed commitments without retyping formulas. If your sheet formulas are fine, you do not need it for this.

Closing checklist

  • Spellings of Rent and EMI match the category column exactly.
  • Blank category rows are counted or excluded on purpose.
  • Grand total equals everyday total plus excluded totals.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports