HOW TO CREATE RUNNING TOTALS BASED ON CATEGORIES

You can use OFFSET to create a "sort-safe" formula, like this:

No need to wrap the formula with IFERROR. Just put the 0 in the 'if-not-found' parameter for XLOOKUP, as I've done in the formula above. (I had "NF" in the original formula in the previous post.)

SG

bzabbia wrote:

I'd like to create a Running Total column in my TRANSACTION Table based upon CATEGORIES and upon the previous End of

It's hard to tell from your screenshot what you are trying to do. But from your description, are you trying to do something like this?

Here, it's assumed you have the end of fiscal year in cell B1 of the Transaction Summary table.

Then the formula in F2, filled down would be:

=SUMIFS(E$1:E2,D$1:D2,D2,B$1:B2,">="&Transaction Summary::$B$1)

Keep in mind that a Pivot Table might be a much quicker and easier way to analyze your data (with no formulas!) Click in the data table and from the menu choose Organize > Create Pivot Table > On Current Sheet. See 'Pivot Table Basics' template at File > New in your menu for working examples.

SG

Thanks for the clarification! You can try something like this:

The formula in F2 of the Transactions table, filled or copied down the column:

=XLOOKUP(D2,Savings Summary::A,Savings Summary::B,"NF")+SUMIF(D$1:D2,D2,E$1:E2)

This has "expanding ranges" to calculate the running total.

The formula in C2 of Savings Summary, filled down:

=XLOOKUP(A2,Transactions::D,Transactions::F,"NF",0,-1)

The -1 means search from last to first. That way it finds the last entry for that category in the Running Total column.

SG

I suggest a couple changes to your table:

1) remove the TRANSACTION column

2) add a new column AFTER the column named AMOUNT. Call this DEPOSIT

3) rename the column AMOUNT as EXPENSE

should look something like:

the text you entered and the sig of the number in your example conflict, so, I made an attempt to make the example consistent... that is "Eating out" is always an expense... unless you are meaning it as an indication of a business expense that was reimbursed.

Now use the built-in Numbers feature Categories as follows:

1) select the table

2) open the table Organizer panel then click the "Categories" segment:

3) Add a category and choose the "CATEGORY" column:

you should get this:

5) add category summaries as needed. In the above screenshot, I already selected one possible location. Select the Expense header for any CATEGORY, then choose from the contextual menu:

If you do not want to see the table arranged like this, turn off categories:

Which question? You've kept moving the goal post, so it's hard to give helpful answers.

SG