Countifs with date range and criteria
Hi, I am trying to do a formula to count the # of rows the have "Waller" as the L3 leader between the dates of Nov 1, 2020 and January, 31, 2021. I am trying to create a these metrics in a separate sheet.
This is what I have but it is not adding.
=COUNTIFS({Sheet - NA Market Travel Approval Range 3}, {Sheet - NA Market Travel Approval Range 3} = "Waller", {Sheet - NA Market Travel Approval Range 2}, >DATE(2020, 11, 1), {Sheet - NA Market Travel Approval Range 2}, <=DATE(2021, 1, 31))
Thanks in advance.
Best Answer
-
Thanks for the research. I do have Waller matched exactly. The date is a system date which is the date created and the format is the following. Wondering if that is the problem 07/24/21 6:15 AM.
Answers
-
Should your first DATE section be ">=" instead of just ">"?
=COUNTIFS({Sheet - NA Market Travel Approval Range 3}, {Sheet - NA Market Travel Approval Range 3} = "Waller", {Sheet - NA Market Travel Approval Range 2}, >=DATE(2020, 11, 1), {Sheet - NA Market Travel Approval Range 2}, <=DATE(2021, 1, 31))
-
I changed to ">=" and it still had a count of 0.
-
Hi @Kathleen Howarth ,
I used your formula with my own data (where Range 3 was a column with names and Range 2 was a column with dates) and was able to get the correct counts.
Two things to check:
In the column "Sheet - NA Market Travel Approval Range 3" is "Waller" listed exactly like that, with no extra spaces or other characters?
Is the column "Sheet - NA Market Travel Approval Range 2" formatted with a Column Type of Date?
-
Thanks for the research. I do have Waller matched exactly. The date is a system date which is the date created and the format is the following. Wondering if that is the problem 07/24/21 6:15 AM.
-
I was only including a portion of the name in the column and that fixed it! thanks so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!