Spreadsheets

IF and AND formula to tag small, medium and large expenses

Tag each expense as Small, Medium or Large using IF, AND, IFS or a LOOKUP band table in Excel, then count and total spending by size.

Editorial illustration for IF and AND formula to tag small, medium and large expenses

The tidiest way to tag expenses as Small, Medium or Large is a small band table plus one LOOKUP formula, not a nest of IF statements. Write the lower limit of each band beside its name, and =LOOKUP(D2,$H$2:$H$4,$I$2:$I$4) returns the right label for any amount.

What are the three ways to write it?

Say Small is under ₹500, Medium is ₹500 up to but not including ₹2,000, and Large is ₹2,000 and above. These limits are hypothetical, so set your own. With the amount in D2:

  • Nested IF: =IF(D2<500,"Small",IF(D2<2000,"Medium","Large"))
  • IF with AND: =IF(AND(D2>=500,D2<2000),"Medium",IF(D2<500,"Small","Large"))
  • IFS (Excel 2019 or 365, and Google Sheets): =IFS(D2<500,"Small",D2<2000,"Medium",TRUE,"Large")

All three give the same answer. The trouble comes later. When you decide Medium should start at ₹750, you must edit the formula in every place it appears, and the more bands you add, the more brackets to close.

How does the LOOKUP band table work?

Put the bands in a small table somewhere out of the way, say H1:I4:

From (₹)Label
0Small
500Medium
2000Large

Enter =LOOKUP(D2,$H$2:$H$4,$I$2:$I$4) and fill down. LOOKUP finds the largest From value that is less than or equal to the amount and returns the label beside it. The From column must be sorted lowest to highest, or the answer is unreliable. Changing a limit now means typing one number in the table, and every tag updates. Adding a band called Huge at 10,000 is a new row, and you extend both ranges.

Check the edges with hypothetical amounts: 120 gives Small; 499.99 gives Small; 500 gives Medium; 1,999 gives Medium; 2,000 gives Large; 15,000 gives Large.

How do you pick the limits? Let your own data suggest them. =MEDIAN(D2:D500) shows the middle expense, and =PERCENTILE.INC(D2:D500,0.9) shows the amount that only one spend in ten exceeds. A reasonable start is Small below the median, Large above the ninety percent mark, and Medium between. Then adjust until the Large list is short enough to review in five minutes.

What happens with refunds and negative amounts?

A negative amount sits below the first From value of 0, so LOOKUP returns #N/A. Decide how you want refunds treated. If you want the tag to reflect size and ignore direction, use =LOOKUP(ABS(D2),$H$2:$H$4,$I$2:$I$4). If refunds should be untagged, wrap the formula: =IF(D2<0,"Refund",LOOKUP(D2,$H$2:$H$4,$I$2:$I$4)).

How do I count and total by band?

With tags in column E, a summary only needs two functions:

  • Number of Large expenses: =COUNTIFS($E$2:$E$500,"Large")
  • Rupees in Large expenses: =SUMIFS($D$2:$D$500,$E$2:$E$500,"Large")
  • Share of spending in that band: =SUMIFS($D$2:$D$500,$E$2:$E$500,"Large")/SUM($D$2:$D$500)

Format the last one as a percentage. A pattern many people notice is that dozens of Small payments, such as UPI taps for tea, snacks and rides, can add up to more than a few Large ones. Your own table will show whether that is true for you, which is the point of tagging.

A three-row summary table, with a label, a COUNTIFS and a SUMIFS for each band, takes about two minutes to build and tells you where the month's money went. Compare it with last month's table, and the change in the Medium band often tells you more than the change in the total.

How do I colour the tags?

Select the tag column, open Home, Conditional Formatting, New Rule, then Use a formula. Enter =$E2="Large" and choose a fill colour. Repeat with "Medium" and "Small" in softer shades. In Google Sheets, the same rule lives under Format, Conditional formatting, with Custom formula is, and it uses the identical formula. Colour makes a month of data readable at a glance.

Quick checklist

  • Band table sorted ascending.
  • A first band starting at 0 or lower.
  • Edge amounts tested.
  • Refund rule decided.

If you track this in an app, SpendixAI offers advanced filters and custom reports on Pro, so size-based views can be built without formulas.

SPENDIXAI JOURNAL

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

Explore your reports