how to find the last table range of a specific value in Numbers

ApyJo-Bg wrote:

How do I insert a "Spent" formula in the Summary Sheet to show the gasoline expenses from 6/11 to 6/14 only?

=SUMIFS(Transactions::D,Transactions::C,"Gasoline",Transactions::E,"CC", Transactions::A,">="&DATE(2025,6,11),Transactions::A,"<="&DATE(2025,6,14))

Your formula had all but the dates in it. I used the DATE function to put the dates into the formula here but you will probably reference two cells that have dates in them. I thought you wanted to check an entire week of dates but your final question was for only the four days 6/11-6/14.

Here are some approaches that may give you some ideas.

Formula to get the most recent Thursday:

=TODAY()−MOD(WEEKDAY(TODAY(),2)−4,7)

Simply subtract 7 to get the Thursday before that.

If you are running Numbers 14.4 or later then you can populate an intermediate filtered table with one formula, which spills results into the other cells in the table:

=FILTER(Transactions::A:D,(Transactions::A>Thursdays::B3)*(Transactions::A≤Thursdays::B2))

Then you can use SUMIF on that intermediate table to sum for, say, "Gasoline":

=SUMIFS(Filtered::D,Filtered::C,A2)

Simple and straightforward.

I tried getting fancy by using a LET formula to try to dispense with the intermediate filtered table and the table calculating Thursdays. This worked:

=LET(latestthurs,TODAY()−MOD(WEEKDAY(TODAY(),2)−4,7),prevthurs,latestthurs−7,MAX(SUMIFS(Transactions::D,Transactions::A>@prevthurs,Transactions::A≤@latestthurs,Transactions::C,@$A$2)))

Things started to get complicated.. To prevent it from "spilling" identical values I used MAX as a hack. The explicit intersection operator @ didn't prevent the spilling.

And then there is still the "old school" formula with the original 'Transactions' table and the 'Thursdays' table:

=SUMIFS(Transactions::D,Transactions::A,">"&Thursdays::B3,Transactions::A,"<="&Thursdays::B2,Transactions::C,A2)

All of these approaches will update dynamically.

Another approach would be to enter the relevant Thursday in a separate column for each transaction and simply use the built-in Category feature to group and sum an that column and the Category, similar to what Camelot has shown above. Not fully automatic. But efficient.

SUMIFS - Apple Support

LET - Apple Support

MOD - Apple Support

SG

> The goal is to find the total expense for a field within a range for the most recent week

The key here is to understand what you mean by that. While it might seem obvious, there are actually a few ways of interpreting this.

For example, do you mean literally" in the last 7 days"?

Or do you mean "in the previous Sunday-Sunday window"?

or maybe "since last Sunday"?

Ultimately, your solution is likely to be an extension of the SUMIFS() you already have, just with some additional conditions.

For example, let's say you want to add "within the last 7 days" to your existing formula:

The tricky part (that you might have already tried) comes in how you pass dates into the comparison. The easiest way to do it is via LET. For example:

This creates a variable called targetdate which is calculated as ">=" coupled with the date 7 days prior to today ( TODAY()-7 ). This can then be incorporated into your SUMIFS() formula.

The nice thing about this is that it's easy to extend. For example, if you want to base it on a different date you can just edit the targetDate variable. it could even refer to a cell in the table where you enter the date you want.

If you want to work on week numbers, that's actually even easier, since Numbers has built-in functions for calculating the week of the year:

Here the comparison is checking the WEEKNUM() of the values in column A against the WEEKNUM() of today (again, this could be a reference to some other date.

When all said and done, though, you might want to consider a completely different approach, namely using Categorizations. Numbers has built-in tools for analyzing data like this and creating logical groups, subtotals, etc. for your data.

For example, taking your sample data and clicking on Inspector -> Organize -> Categories it's possible to get Numbers to tabluate this data with just a few clicks:

From your original post:

"find the total expense for a field within a range for the most recent week"

From your latest post:

"show all expenses paid for belonging to a particular category from week to week beginning each Thursday"

Those are two different things.

And what app are you using? Your screenshot seems to be of Excel, not Numbers.

Perhaps you could post a screenshot, of Numbers, showing sample data and what you would like to do with it.

SG

It's hard to visualize what you are doing (no 'Bookkeeping' table in your screenshots) and which suggestions, if any, you might have tried will remain a mystery. But, anyway, good luck with your project.

SG