COUNTIFS Formula Assistance - How to count date ranges
I am trying to create a COUNTIFS formula that has 4 ranges/criterions that pull references from another sheet. Below is the current formula. It was working right prior to adding the date ranges but I cannot figure out what is going wrong. The formula still works and pulls in 0 but that is not accurate, it should be coming out as 9.
- Range 1: In this column, if the cell is 1 (checked box), count it
- Range 2: In this column, if the cell equals "Site", count it
- Range 3/4: In this column, if the cell is greater than or equal to this cell (date of 03/13/22) and lesser than or equal to another cell (date 03/20/22), count it
=COUNTIFS({Campaign Referral Tracker Range 1}, 1, {Campaign Referral Tracker Range 2}, "Site", {Campaign Referral Tracker Range 8}, >=[Week 2]3, {Campaign Referral Tracker Range 8}, <=[Week 2]4)
Answers
-
Follow up: I was able to make the formula work with the below updates (instead of cell reference for the date ranges, using the date formula). However, the goal is to be able to copy/paste the entire formula with the cell links so that I don't have to manually update the formula dates every time.
Any ideas on how to do that with a cell reference instead of the date formula?
=COUNTIFS({Campaign Referral Tracker Range 1}, 1, {Campaign Referral Tracker Range 2}, "Site", {Campaign Referral Tracker Range 8}, >=DATE(2022, 3, 13), {Campaign Referral Tracker Range 8}, <=DATE(2022, 3, 20))
Thanks! 😁
-
Is the [Week 2] column set as a date type column? If so, how are those dates being populated?
-
Hi Paul, it is not set as a date column (but can be changed to this) and the dates are manually populated. Would changing the column type to a date column, then updating the formula to pull from the cells solve this?
-
So I played around with this by creating a text date column and updating the COUNTIFS to be greater than/equal to and lesser than/equal to the cells that are now formatted as dates. The error I'm running into now is the "date expected" error. How to I circumvent this issue?
-
Hi @kleinsb1
If you're searching for a specific date in {Campaign Referral Tracker Range 8} and this range is a Date Column, then yes, the values you're referencing ([Week 2]) will also need to be in a Date type of column. This is why using the DATE function works, but referencing a text cell does not.
Since you're still getting an error, can we confirm that {Range 8} is a Date Type as well?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
The columns you are REFERENCING should be the date type columns, but the formula itself should be going into a text/number type column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!