COUNTIFS Formula Assistance - How to count date ranges

kleinsb1

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)

kleinsb1

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! 😁

Paul Newcome

Is the [Week 2] column set as a date type column? If so, how are those dates being populated?

kleinsb1

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?

kleinsb1

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?

Genevieve P.

https://community.smartsheet.com/discussion/comment/327238#Comment_327238

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?

Paul Newcome

The columns you are REFERENCING should be the date type columns, but the formula itself should be going into a text/number type column.