CountIfs results 0

kmorsesmartsheet

My sheet currently has a formula tracking # of job submissions - formula had been working up until today. It now results in zeros - I'm thinking it might be the year change however original formula uses the date of Today for year and month so I'm not certain the year is the problem. Formula below - any suggestion would be helpful- thank you:

=COUNTIFS({Design Request Tracker Range 1}, IF(ISDATE(@cell), YEAR(@cell)) = YEAR(TODAY()), {Design Request Tracker Range 1}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY()) - 1)

Carson Penticuff

You are correct in that the rollover to 2025 is causing your issue. The formula, as it stands, is looking for dates within the current year, then subtracting one month from today. Since today is month 1, it is looking for 1-1 = month 0.

Give this a try, it should take year rollovers into account:

=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY() - DAY(TODAY()) - 1), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY() - DAY(TODAY()) - 1))

Carson Penticuff

The current month is simpler due to not having to account for the possibility of wrapping into a different year.

=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY()), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY()))

Carson Penticuff

You are correct in that the rollover to 2025 is causing your issue. The formula, as it stands, is looking for dates within the current year, then subtracting one month from today. Since today is month 1, it is looking for 1-1 = month 0.

Give this a try, it should take year rollovers into account:

=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY() - DAY(TODAY()) - 1), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY() - DAY(TODAY()) - 1))

kmorsesmartsheet

Thank you - problem solved!!!!

kmorsesmartsheet

To further this question I now want to adjust above to pull the data from December 1-31 what adjustments need to be made to this formula to now read the 2024 data?

Carson Penticuff

Maybe I misunderstood your initial question. The formula I posted should count all entries for the previous month. For the duration of January 2025, it should count all entries dated December 2024.

kmorsesmartsheet

Forgive me I'm working off of an smartsheet created by a former employee - the column was labeled as the current month, but apparently it was collecting data totals for the prior month. OK - so if if I'm looking for current month then I should be able to remove the -1 which would give me a running count through January as jobs/rows are added - is that correct?

Carson Penticuff

The current month is simpler due to not having to account for the possibility of wrapping into a different year.

=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY()), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY()))

kmorsesmartsheet

Fabulous - that did the trick! Thank you again!!

Carson Penticuff

Awesome, happy to help. 👍