CountIfs results 0
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)
Best Answers
-
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))
-
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()))
Answers
-
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))
-
Thank you - problem solved!!!!
-
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?
-
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.
-
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?
-
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()))
-
Fabulous - that did the trick! Thank you again!!
-
Awesome, happy to help. 👍
-
Multipart question - To further this conversation, As this answer has been helpful in applying these changes to other sheets I've run into one formula where I'm unable to identify what needs to change for the current month data:
Current Look back formula is:
=COUNTIFS({Design Request Tracker Range 2}, ISDATE(@cell ), {Design Request Tracker Range 2}, YEAR(@cell ) = YEAR(TODAY() - DAY(TODAY()) - 1) - 1, {Design Request Tracker Range 1}, [Primary Column]@row)@cell
What should change to this for current month data?
In addition - in regards to the earlier question and resolution regarding the look back adjusting for the year change I'm now tasked to provide data for 1, 2 & 3 month results.
Current Look back formula for 1 month is:
=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), {Design Request Tracker Range 2}, true)
What should change to this for 2 mos look back and a 3 mos look back? I've tried to just change the -1 to a -2 but that has not worked.
-
I have no issue admitting this took me way longer to figure out than I would have thought; working with dates can get very confusing.
The formula I posted previously should always work for looking at the previous month, however, it isn't "exactly" correct. The -1 inclusions cause the formula to look at the next to the last day of the previous month instead of the last day. Since all months have more than two days, this doesn't really change anything… it is just confusing if you try to deconstruct the formula to see how it works.
REVISED FORMULA FOR THE PREVIOUS MONTH
=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY() - DAY(TODAY())), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY() - DAY(TODAY())), {Design Request Tracker Range 2}, true)TWO MONTHS IN THE PAST
=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY())))), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY())))), {Design Request Tracker Range 2}, true)THREE MONTHS IN THE PAST
=COUNTIFS({Design Request Tracker Range 1}, ISDATE(@cell), {Design Request Tracker Range 1}, YEAR(@cell) = YEAR(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY()))) - (DAY(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY())))))), {Design Request Tracker Range 1}, MONTH(@cell) = MONTH(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY()))) - (DAY(TODAY() - (DAY(TODAY())) - (DAY(TODAY() - DAY(TODAY())))))), {Design Request Tracker Range 2}, true)I have this working in a test sheet, so it should work okay. If it doesn't, just let me know.
-
Thank you for your response Carson - I'll give it a try later today and report back! You've been most helpful with ALL of your responses!
-
I'm not quite sure the adjustment for the previous month needed adjusting - it's taken my count which appeared to be accurate when you'd provided the earlier formula to a total of 18 counts, which is way off. I haven't been able to determine where the 18 might be coming from to investigate the causing issue. I'm thinking it's missing the -1 look back for the year since this is January? You've been most helpful and I can fiddle with it more next week as I'm heading our for a long weekend. Thank you for your responses and help! I'll report back if I'm able to discern the issue.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi all, I'm struggling to get a formula to work that can return the number of projects that start within the next 4 weeks. In the main sheet we have a project type column and a project start date colu…
- Hi all! I know how to do this manually but am hoping someone has a shortcut! I have a sheet with a very long dropdown list of college majors for employees who have signed up for a particular program. …
- New program proposals are submitted via a form and collected on Sheet 1. When approved by a team leader, the row is copied to Sheet 2. I want to capture the date the copied occurred, which I thought w…
