Please correct =SUMIFS formula

With spreadsheets adopting an efficient format for data entry can make like a LOT easier. When you find you're having to resort to using INDIRECT that's often a sign that you should look at ways to simplify your document..

Why not lay out your expenses in a simple list format similar to what you see in the Transactions table of the 'Personal Budget' template at File > New in your menu? It could look something like this:

When you do that, you can easily extract reports from your data without formulas at all, let alone complicated formulas with INDIRECT.

For example, I produced this in less than a minute:

All you have to do is click in the table with the data, from the menu choose Organize > Create Pivot Table > On Current Sheet, then drag what you want from the Fields box down into the Columns, Rows, and Values boxes as shown in the screenshot.

You can easily experiment and change things around until you get what you want. Doing that will not affect your original data. You can also have more than one Pivot Table at the same time if you want to see your expenses in different ways at the same time.

You can summarize by month, by quarter, by week, etc., again without worrying about a single formula:

After you add or change data to your table containing your list of expenses simply click in the Pivot Table and choose Refresh Pivot Table from the Organize menu.

SG

You are right that it does not work in two dimensions the way you are trying to make it do it.

Your formula is summing up the numbers in the "eggs" column so there is no need to put the condition of "eggs" as one of the conditions. If you leave that last bit off the formula it will work I think. If you are trying to sum the values for an entire month, you may want to use ">="&G$1 as the second condition so it includes the first day of the month.

=SUMIFS(Expense::Table 2::$D$1:S161,Expense::Table 2::$C1:$C161,"<"&H$1, Expense::Table 2::$C1:$C161,">="&G$1)

I assume, though, that you are trying to make a formula that you can fill down to the other rows that will use the words from the "expenses" column ("eggs" or "yogurt", etc. ) vs you having to edit the formula each time. The following formula might work. It relies on the "use header names as labels" feature of Numbers (which does not have to be on for it to work). I am assuming you are referencing the entire column when you use D1:D161 so I will use the "entire column" type referencing. And I am assuming "expenses" in your lower table is column B.

=SUMIFS(INDIRECT("Expense::Table 2::"&B),Expense::Table 2::C,"<"&H$1, Expense::Table 2::C,">="&G$1)

As long as there is only one column in Expense::Table 2 that is headed by each word ("eggs", "yogurt", etc.) this should work. I did not recreate your two tables to test the formula but I tried something similar in a table I made up. I feel like I am misusing the "use header names as labels" feature but it seems to work and it is a lot less complicated than other formulas I was thinking of.

Here a small addition to the version from Badunit

In his version you need 2 cells to define the date range.

=SUMIFS(INDIRECT("Expense::Table 2::"&B),Expense::Table 2::C,"<"&H$1, Expense::Table 2::C,">="&G$1)

This could give an error when your table ends with December 2022, because this formula would need January 2023.

If you use this version your table can end with December 2022, because I am using only one cell for the date range.

=SUMIFS(INDIRECT("Table 1::"&$B3),Table 1::$C,">="&C$1,Table 1::$C,"<="&EOMONTH(C$1,0))

Table 1::$C,">="&C$1 will check if Purchase date is >= Jan 1st

Table 1::$C,"<="&EOMONTH(C$1,0)) will check if Purchase date is <= Jan 31st

EOMONTH(C$1,0) is returning the last day of that month.

Row 1 should be formatted that only month or month & year will be shown, full date is only used to make it easier to understand.

Ralf