Personal Budget

Two things would help when asking for help:

• Show the table titles so we can know where data is coming from (in the formulas)
• Show all relevant data (like the table on sheet Data)

Anyway I was able to deduct it. I think your formulas are overly complicated.

For monthly compilation:

=SUMIFS(Data::Table 1::$E,Data::Table 1::$Categories,$A2,Data::Table 1::$A,">="&Table 2::$C$2,Data::Table 1::$A,"<="&EOMONTH(Table 2::$C$2,0))

For yearly compilation:

=SUMIFS(Data::Table 1::$E,Data::Table 1::$Categories,$A2,Data::Table 1::$A,">="&DATE(Table 2::$B$2,1,1),Data::Table 1::$A,"<="&DATE(Table 2::$B$2,12,31))

As for showing - instead of 0, use a custom format like we discussed in another thread. Do not put a hard dash because it messes up all calculations that rely on that cell.

All in one.

=SUMIFS(Data::Table 1::$B,Data::Table 1::$Categories,$A2,Data::Table 1::$A,">="&DATE(Table 1::$B$2,IF(Table 1::$A$2="ALL",1,MONTH(Table 1::$A$2)),1),Data::Table 1::$A,"<="&EOMONTH(DATE(Table 1::$B$2,IF(Table 1::$A$2="ALL",12,MONTH(Table 1::$A$2)),1),0))

I hope you study it carefully to understand the logic and apply it to your future projects.

You dont need the third column of the first table any more, with the date (9/1/23 in your picture).

=SUMIFS(Data::Table 1::$G,Data::Table 1::$F,$A2,Data::Table 1::$A,">="&DATE(Table 2::$B$2,IF(Table 2::$A$2="ALL",1,MONTH(Table 2::$A$2)),1),Data::Table 1::$A,"<="&EOMONTH(DATE(Table 2::$B$2,IF(Table 2::$A$2="ALL",12,MONTH(Table 2::$A$2)),1),0))

You have to understand formulas to be able to correct according to your local conditions. Please study them.

Now you have work assignments:

1- When posting pictures of your spreadsheets ALWAYS show the title of your tables. You can hide them for yourself afterwards but without them it's very hard for us to figure out where data is coming from. If tables are on multiple sheets also show the name of the sheets.

2- Use meaningful names for your tables, like Expenses (on sheet Data), Calendar, Summary. It makes it easier to identify the source of data.

3- Use header rows for your tables. It makes it easier to identify the source of data. When you build your formulas that use a whole column, click on the column's letter to select it.

If you did all this, the formula above would be this:

=SUMIFS(Expenses::$Amount,$category,$A2,Expenses::$A,">="&DATE(Calendar::$B$2,IF(Calendar::$A$2="ALL",1,MONTH(Calendar::$A$2)),1),Expenses::$A,"<="&EOMONTH(DATE(Calendar::$B$2,IF(Calendar::$A$2="ALL",12,MONTH(Calendar::$A$2)),1),0))

Much easier to troubleshoot, isn't it? If you don't like the names I used, set them to paste that formula and change them afterwards, the formulas will adapt.

4- Again: for showing - instead of 0, use a custom format. Do not put a hard dash because it messes up all calculations that rely on that cell. You want to keep 0 as a value.

What do you think that DATE(Table 2::$B$2,1,1) and DATE(Table 2::$B$2,12,31) are in the yearly formula I proposed?

January 1st and December 31st.

Again: for showing - instead of 0, use a custom format like we discussed in another thread. Do not put a hard dash because it messes up all calculations that rely on that cell. You want to keep 0 as a value.

Hi Raul.

1- Do not make these formulas where you put a dash (-) in the cell when the result is 0. Mixing numbers and text is rarely a good idea. As we discussed before, if you want a dash to represent 0, use a custom format, see a previous message from October 27th on how to do it.

Therefore, your first formula can be simplified to the test of your IF:

=SUMIFS(Expenses::Amount,category,$A3,Expenses::$date,">="&DATE(Calendar::$B$2,IF(Calendar::$A$2="ALL",1,MONTH(Calendar::$A$2)),1),Expenses::$date,"<="&EOMONTH(DATE(Calendar::$B$2,IF(Calendar::$A$2="ALL",12,MONTH(Calendar::$A$2)),1),0))

2- -$154 does not affect the balance. If indeed that value is in the summing range, my guess is that it's a text and not a number. Do not type -$154, just -154 will suffice, Numbers should take care of adding the currency sign and will treat it as a numerical value. If the currency sign does not automatically appear, format the whole column as currency.

3- I'm not sure which cell A3 you're talking about. Do you mean B3, where is the 5765,63 ? Wouldn't it be enough to just add the values of column B in the Summary table?

=SUM(Summary::B)