SUMIFS formula with condition on dates stopped working

Question

jimjiminyjim Author

Level 1

14 points

SUMIFS formula with condition on dates stopped working

Hi all,

Thanks in advance for your help.

I had a set of formulas for tallying amounts from a given month.

They stopped working sometimes over the new year and all my values started returning 0.

I reverted to older versions to no avail.

It seems like using two separate date conditions is the problem.

This is the formula that was working but is no longer.

SUMIFS(Debit,statement::Category,F$1,Date,">="&$A10,Date,"<"&EDATE($A10,1))

Questions to help me figure this out?

Jim

MacBook Pro

Posted on Jan 14, 2021 7:29 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,134 points

Jan 14, 2021 7:36 AM in response to jimjiminyjim

What is in A10? Is that formatted as Date-Time (or Automatic)? If so, even if you have just a month name there (with no day or year visible), Numbers will supply a day and year if you don't specify those. So perhaps you still have last year's year in there? The way to tell is to click the cell and look in the lower left corner in Numbers.

To fix the problem, you can consider either going into the cell and changing the year, or (if you want Numbers always to assume the current year) then you can format the cell as Text and enter a month name again.

SG

Reply

Answer 2

jimjiminyjim Author

Level 1

14 points

Jan 14, 2021 8:30 AM in response to SGIII

The formula references two sheets. The first sheet shows the date formate in the second column.

The second sheet shows the formula. All through last year, those values were up to date, now all reading zero.

Reply

Answer 3

jimjiminyjim Author

Level 1

14 points

Jan 14, 2021 7:43 AM in response to SGIII

Thanks SG, yes, I have just a month name in A10 (example January), and when clicked it reads as 2020-01-01 under Actual. It is formatted as Automatic. I had wondered about it being to do with the year change over but I can't figure out how, as the column referenced in the formula as "Date" are also 2020 year dates. Any other ideas?

Reply

Answer 4

Barry

Level 9

59,951 points

Jan 14, 2021 4:55 PM in response to jimjiminyjim

The only category in the listed transaction in the first table is "invoiced sale".

In the Accounts table, the formula displayed in the box shows "F$1", indicating the that column will contain the sum of all transactions between the specified dates whose category is "Milk". I don't see a column whose category is "invoiced sale," which would be necessary to show a sum including the value in the only filled row shown of the first table.

It's possible that the reason for zero sums is not due only to date disagreement between the two tables.

Regards,

Barry

Reply

Answer 5

jimjiminyjim Author

Level 1

14 points

Jan 15, 2021 8:03 AM in response to Barry

Thanks for checking in Barry. I do have milk categories. The item I showed was just one of about 600 to give the framework. Didn’t really want to share a whole page full of my financials. The table full of zeros was working before - properly tallying the milk category as well as others. Any other thoughts?

Reply

Answer 6

jimjiminyjim Author

Level 1

14 points

Jan 15, 2021 9:49 AM in response to SGIII

Hi SG,

Your solution was in the end the answer. I should have tried it. There seems to be a bug where specifying "January" as a date, shows in the info as "Actual January 1, 2020" but somewhere behind the scenes is still registering as January 1, Current Year. When I manually specified Jan 1 2020, that did fix all the formulas. Just seeing that it was apparently registering 2020 made me not try this fix. I spent so much time trying to puzzle this out!! Oh well, thanks again. Could have been a lot longer without your suggestion.

Best,

Jim

Reply