Spreadsheets

FILTER in Google Sheets by category and month

Filter expenses in Google Sheets by category and month with one FILTER formula, using multiplied conditions, a date range and an All option.

Editorial illustration for FILTER in Google Sheets by category and month

In Google Sheets, FILTER takes several conditions at once: =FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)*(C2:C500=H3)). Each condition gives TRUE or FALSE per row, and multiplying them means a row must pass every test. Change the month, year or category in the input cells and the list follows.

What do the conditions look like?

Say your sheet has the date in A, merchant in B, category in C and amount in D. Put your choices in input cells: H1 for the month number, H2 for the year, H3 for the category. Then each part of the formula has a job:

ConditionWhat it testsExample input
MONTH(A2:A500)=H1Date falls in the chosen monthH1 = 4
YEAR(A2:A500)=H2Date falls in the chosen yearH2 = 2026
C2:C500=H3Category equals the chosen nameH3 = Groceries

You can also separate conditions with commas, as in FILTER(A2:D500, cond1, cond2, cond3). Sheets treats that as AND as well. The multiplication style is useful when you later want OR, which is addition: (cond1)+(cond2) is TRUE when either holds.

What happens when nothing matches?

Sheets FILTER has no built-in "if empty" argument. When no row passes, it returns #N/A. Wrap it so the sheet says something kinder:

=IFNA(FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)*(C2:C500=H3)),"No matching expenses")

How do I filter by a date range instead of one month?

For "the first to the fifteenth", or any period, put a start date in H4 and an end date in H5:

=IFNA(FILTER(A2:D500,(A2:A500>=H4)*(A2:A500<=H5)),"No matching expenses")

Both ends are included. If the dates in column A are text rather than real dates, the comparison fails and you see no rows. Click a date cell; right-aligned values are usually real dates, left-aligned ones usually text.

How do I add an All categories option?

A single category test would hide everything else. To let H3 hold either a category name or the word All, use REGEXMATCH as a wildcard:

=IFNA(FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)*REGEXMATCH(C2:C500,IF(H3="All",".*","^"&H3&"$"))),"No matching expenses")

When H3 is All, the pattern .* matches any text. Otherwise the pattern "^Groceries$" matches only a cell that is exactly that. One catch: if a category name contains brackets or a dot, regex reads them as special characters. In that case drop REGEXMATCH and use ((C2:C500=H3)+(H3="All")>0), which does the same job without patterns.

What can go wrong?

  • Mismatched range sizes: every range inside FILTER must have the same number of rows, so A2:D500 pairs with A2:A500, never A2:A400
  • Text in the month cell: MONTH returns a number, so H1 must be a number, not April
  • Dates stored as text: test one with =ISNUMBER(A2)
  • Results blocked: if a cell below the formula holds something, you see a #REF! telling you the array could not expand

Add a total for what you see

To show the total of the same filtered rows, filter the amount column and sum it:

=SUM(IFNA(FILTER(D2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)*(C2:C500=H3)),0))

Say Groceries in April holds ₹2,300, ₹640 and ₹1,150. The total reads ₹4,090, and a month with no groceries returns 0 rather than an error.

Can I sort the filtered list as well?

Wrap the whole thing in SORT, giving the column number and the order. To list the filtered rows with the biggest amount first, where the amount is the fourth column of the range:

=IFNA(SORT(FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)),4,FALSE),"No matching expenses")

Sort by column 1 with TRUE to see the oldest date first. Because SORT sits outside FILTER, the order is applied after the rows are chosen, which is what you want.

Ready-to-use checklist

  1. Make sure column A holds real dates
  2. Create the three input cells with dropdowns
  3. Paste the formula in a cell with room below it
  4. Wrap it in IFNA
  5. Check the total against the category's total on your summary

If you find yourself building a new filter view for every question, a report tool that filters and groups transactions for you may suit better, and SpendixAI's Report builder does exactly that with a CSV download.

SPENDIXAI JOURNAL

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

Explore your reports