COUNTIF with dates pulled from cells?
Hello!
I'm trying to run a count of the number of entries on a sheet, where the "needs analysis assigned date" is greater than 5 days prior to the "date requestor contacted" date for that same row of data.
Example:
Row 1:
Needs analysis assigned date: 11/1/2022
Date Requestor contacted: 11/2/2022
The date difference only being 1 day, this should not be counted.
However, for:
Row 2:
Needs analysis assigned date: 11/1/2022
Date Requestor contacted: 11/7/2022
This should be counted as it's been 6 days.
My formula is below, and it's #UNPARSEABLE. can anyone help?
=COUNTIF([Date Requestor Contacted]:[Date Requestor Contacted], <=[Needs analysis assigned date](-5)@cell)
Best Answer
-
Hi @David Acord
I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:
=IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))
Cheers!
Genevieve
Answers
-
Hi @David Acord
The way I would do this is to have a helper checkbox column hidden on the sheet with a Column Formula applied that would check each row for these conditions. If it's true, it would check the box. Then your COUNTIF can simply count how many checkboxes there are!
Try something like this as a column formula:
=IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0)
Then your COUNTIF would be:
=COUNTIF([Helper Column]:[Helper Column], 1)
Cheers!
Genevieve
-
Fantastic! Thanks @Genevieve P. - this is definitely working. I've noticed a hiccup (at least for me) - this will also check the box if there's no date entered in either of those date columns, such as if the row has incomplete data. Do you have any suggestions on how to not count those/not check the box until those two dates are filled in?
-
Hi @David Acord
I'm glad this is on the right track for you! Good point about blank dates. Yes, lets add in another statement to make sure this doesn't happen:
=IF(OR([Date Requestor Contacted]@row = "", [Needs analysis assigned date]@row = ""), 0, IF([Date Requestor Contacted]@row >= [Needs analysis assigned date]@row + 5, 1, 0))
Cheers!
Genevieve
-
That did it! Thanks so much!
-
Wonderful! 🙂 No problem at all.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!