SumIf isn't updating properly unless values are copy/pasted over

Question

Level 1

0 points

SumIf isn't updating properly unless values are copy/pasted over

I am not comfortable sharing my spreadsheet, as it is a budget sheet, but I think I can lay it out clearly at least.

Here is the formula in question:

=IF(B$5−SUM(DEBT :: E12, MONTHLY :: D16, SUMIF(OTHER :: C, A$1, OTHER :: E), SAVING :: D5, ENVELOPE :: B8, GIVING :: C$3) = 0, "Done!", B$5−SUM(DEBT :: E12, MONTHLY :: D16, SUMIF(OTHER :: C, A$1, OTHER :: E), SAVING :: D5, ENVELOPE :: B8,GIVING :: C$3))

What this does is create what's called a 0 dollar budget by doing the following:

get the sum of all debt payments for the month
get the sum of all monthly subscriptions expected
get the sum of all expected one-time payments IF the budget's month matches the month those payments are due (this table is called Other, I know, I'm creative - it's basically things like Nintendo Family Subscription being due in November, etc)
get the sum of all expected savings contributions,
get the sum of all enveloped expenses (food, pets, gas, etc)
get the sum of all expected giving/charity
SUM all of this together and subtract it from total expected income - if it's exactly 0, show Done!, if it's anything else, show the difference

The problem is the third one. The "month" is written out like January in both the A1 cell and it's counter part in the Other table (name, month due, amount - where "month due" is January or April, etc).

This worked very well on the very first sheet created (started last month).

This month, I copy and pasted last months sheet and changed the month in A1 to "February" - but the output value for that formula would not update. The only way I can get it to recheck the Other table is to go to that table and change the "month due" in every column to a random month and then back to it's original month.

I tried making A1 a Pop Up to see if it pulling from a value forced it to go check the Other table, but that did not change the behavior.

iPad Pro

Posted on Jan 28, 2025 9:47 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Jan 28, 2025 10:51 AM in response to KlineMusic

I can understand not wanting to share the budget sheet, but it does make this kind of thing harder.

Specifically, based on your description, my guess is that the problem lies somewhere in the format of the 'month' field on the source table. I think it's a solid guess, but I might be wrong. On that basis:

Numbers will react very differently if cell A1 contains the text string "February", vs. it being a date field with the value '2/1/2025', formatted to show only the Month name.

It's slightly complicated by the fact that Numbers tries to be 'smart' and predict what you're trying to do.

For example, create a new sheet and type 'February' in a blank cell A2.

Numbers will assume you want this to be a date field, internally it will store 'Feb 1, 2025' as the cell value, and 'Month Name' as the format.

To test this, set the formula of cell B2 to:

=IF(A2="February","Yes!", "No")

What do you get? you get 'No'.

Despite cell A2 looking like 'February', it's really a date field that does not match the string "February".

And that is why I think your formula fails - you're comparing date fields to strings, and they're not matching.

There are a couple of ways of addressing this - either at the source data (being consistent about whether your dates are actual date fields or just text), or at the formula level where you need to make adjustments for the field format.

For example, assuming the source data is an actual date field, you can use the MONTH() function to extract the month from it. In the above example:

=IF(MONTH(A2)=2,"Yes!","No")

This extracts the month (as a number) from cell A2 and compares it to 2 (i.e. February).

If you prefer to use month names:

=IF(MONTHNAME(MONTH(A2))="February","Yes!","No")

This takes the MONTH() number from the date field and converts it into a month name and compares that to the string (beware of internationalization issues if you take this route).

This should remove any ambiguity of the date field vs. month name and fix your problem.

Reply

Answer 2

KlineMusic Author

Level 1

0 points

Jan 28, 2025 11:25 AM in response to KlineMusic

Interesting! Okay. I'm playing with it now:

Testing:

Set current budget month (A1) to February (Pop Up, so is it safe to assume this is a forced String?)
Make changes to February item in Other. It updates (makes sense, the row is updated while it's looking)
While still sitting on February as month, made a change to a March charge in Other
Changed A1 to March via pop up - it does NOT reflect the change.
Checked the field value for Other: Month Due - it's "Automatic"
Changing that column to Text (Just going to use the Pop Up here as well - at least that way, it's comparing apples and apples - HA pun intended)
Ran the same test as above - IT WORKS! It updates "behind the scenes" (more accurately, it recalculates on A1 change).

Solved :)

Thank you! I am coming from a MS background and I figured it was a nuance I was missing.

Reply

Answer 3

KlineMusic Author

Level 1

0 points

Jan 28, 2025 10:31 AM in response to KlineMusic

Further research has been done, so editing to add:

Things I have tried:

Closing the application and reopening it
Checking iCloud website
Checking on iPad (app)
Checking on iPhone (app)
Closing all apps and reopening them
Changing the month in A1 (the reference key, string like "January")

The ONLY thing so far that accurately updates that one field has been to change the Month Due (string, ie "January") to something random and back to it's original month. Once I do that, SUMIF is updated.

This has me a little paranoid that even though other SUMIFs seem to be working, what might be not updating as expected. I don't think there are other problems, but I can't help but worry and triple check now.

I saw something from Google's AI search response (which I am fully aware sucks) that there may be a way to tell the tables to refresh automatically, but I can't find any other reference to that ability anywhere.

Reply

SumIf isn't updating properly unless values are copy/pasted over

Similar questions