Why my filter for expenses works but not for year and expense together?

Question

Why my filter for expenses works but not for year and expense together?

I have filtering working for expenses but I can't get it to work filtering both year and expense. Here is a picture of the tables I'm using. Filter Expense works like expected. Expense is a Pop-Up Menu with the Expenses and Years in it. Filter Expense Year is for both expense and year.

Month and the helper Year columns are both formatted as Date/Time.

Any help will be greatly appreciated, Frank

[Re-Titled by Moderator]

Mac mini (M2 Pro, 2023)

Posted on May 22, 2025 3:33 PM

Reply

Answer 1

SGIII

Level 8

37,532 points

May 23, 2025 3:21 AM in response to franks4business

You can filter by year by doing something like this:

=FILTER(Data::A:H,YEAR(Data::A)=A$1)

Note that rather than using a formula you can simply add a Category on the Date column (Organize menu)to get something like this.

If you need more analysis than simple subtotals then you can use a Pivot Table if you put your data in a more "vertical" format like this:

Date Expense Amount

2025-01-01 Rent 1000.00

2025-01-01 Electricity 120.63

2025-01-01 Groceries 119.38

etc.

Like Categories, Pivot Tables automatically aggregate date data by year (or another period).

Intro to categories in Numbers on Mac - Apple Support

Intro to pivot tables in Numbers on Mac - Apple Support

SG

Reply

Answer 2

May 24, 2025 12:19 PM in response to SGIII

I got it to work like I wanted. Turned out to be easier than I was making it.

Filters by Year including All Years, 2025, 2026, 2027. Filters by Expense and totals on the bottom. I'm using a helper column for Years but I suppose I could use the Year Function too but I couldn't get it to work by doing that.

Electricity, Groceries, Miscellaneous use the RANDBETWEEN(100,125)+RAND() to generate dollars & cents.

I tried to upload the Table but it returned an error on upload.

I changed the Formula to use the Columns instead of specific cells making the entire thing dynamic so you only have to enter the new year and the corresponding Year in the J2 Pop-Up Menu.

As always, Thanks for the help and I hope this helps somebody else,

Frank

Reply