COUNTIF and AND formulas
Need to count a number of times a date is less than or equal to a specific date and equal to a value in another column
For example, I have a column "Schedule Status" that is a drop down with either "Completed" or "Scheduled" as choices. I also have a column for start date.
I want to calculate how many dates are less than or equal to 03/31/20 and meet the criteria "Scheduled"
Best Answers
-
I would insert a new calculating column and drop in the following formula:
=IF([Scheduled Status]1 = "completed", 0, IF([Start Date]1 < 31 / 3 / 20, 0, 1))
Then just have a sum of that column, can hide the calculating column afterwards.
-
Try something like this...
=COUNTIFS([Schedule Status]:[Schedule Status], "Scheduled", [Start Date]:[Start Date], @cell <= DATE(2020, 03, 31))
Answers
-
I would insert a new calculating column and drop in the following formula:
=IF([Scheduled Status]1 = "completed", 0, IF([Start Date]1 < 31 / 3 / 20, 0, 1))
Then just have a sum of that column, can hide the calculating column afterwards.
-
Try something like this...
=COUNTIFS([Schedule Status]:[Schedule Status], "Scheduled", [Start Date]:[Start Date], @cell <= DATE(2020, 03, 31))
-
Thank you! That did it.
This community is awesome
-
Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!