Numbers (Advanced) - SUMIFS formula not filtering by date as expected.

Question

Level 1

5 points

Numbers (Advanced) - SUMIFS formula not filtering by date as expected.

Context:

I have 2 tables, the first, "Expenses" is shown in red

The second, "Table 1" is shown in grey/white, with 4 entries to test the functionality of the formula.

I would like the entries from "Table 1" to SUM and populate "Expenses" filtering in to both the correct month and the correct category. After a few hours of research, trial, error and more error, I have arrived at the following scary, but mostly functional, formula.

It is important that the formula can be copied along rows and columns while preserving the correct input data.

Formula:

SUMIFS($Price,Table 1::$A,">=" &B$1,Table 1::$A,"<=" & EOMONTH(B$1,0),$Category,"*" &$A6& "*")

The source, and some explaination, of the formula: https://exceljet.net/formula/sum-by-month

Issue:

The formula works perfectly in all the months of 2020. However, as it goes through to january of 2021, and beyond, it no longer filters by date (including all data regardless of date). It filters by category just fine.

As is often the case with these things I expect I've overlooked something small but important. I'm in over my head and have no idea how to diagnose. I am immensely grateful for any help you can provide.

Sam

Posted on Oct 4, 2020 1:47 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,360 points

Oct 4, 2020 5:02 PM in response to ssmmxx

I am able to duplicate this. It does not happen if the date is shown but does if the cell is formatted to show the month only. I believe what is happening is the formula is taking the displayed value of "January" for the first condition and turning it back into a date by appending the current year to it. This is the same thing that would happen if you used the DATEVALUE function on one of those cells. The second condition is the correct date you expect because the EOMONTH function uses the actual date from the cell, not just the "January" part of it.

You can use ">"&EOMONTH(B$1,-1) as the first condition. That may be better anyway because while you see "January" in the header cell and think it is Jan 1, you might have accidentally typed Jan 2.

It may seem inconsistent/wrong that it is using the displayed value not the actual date value but I think it is consistent. The comparison is a string. If I use a string function or the& operator on a cell, Numbers uses the what is displayed in the cell, not the actual value in the cell.

Reply

Answer 2

ssmmxx Author

Level 1

5 points

Oct 5, 2020 7:23 AM in response to Badunit

Hi thanks for taking the time to recreate this and find a solution. I was pleasantly suprised to see a response so soon!

Your solution looks great and is something I would never have come up with myself! I need some more practice with formulas and inputs to get a better understanding of how the software works.

I will try it out when I get home and let you know if it's solved.

Thank you so much!

Reply