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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That did it! Thanks so much!
-
Wonderful! 🙂 No problem at all.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!