COUNTIFS Formula with CONTAINS in a specific date range only.

MikeMagruder

Hi everyone. I have a sheet with multiple columns. One column indicates the status of a survey and another column indicates the date the survey was distributed. I want to have a formula that will count the number of rows that contains "Survey Closed" in the status column but only for rows that have a specific date (1/1/24 - 12/31/24) in the Distribution Date column.

I've had success with the below formulas to return one count for the number of surveys closed and one count of how many surveys were distributed in Calendar Year 2024:

=COUNTIF({Survey Tracker Range 2}, CONTAINS("Survey Closed", @cell))

=COUNTIFS({Survey Tracker Range 1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 12, 31)))

When I try to combine the two ideas above, and return only the number of surveys with a "Survey Closed" status in CY24, I can't get it to work. I've tried the below but I get an "INCORRECT ARGUMENT" error.

=COUNTIFS({Survey Tracker Range 2}, CONTAINS(@cell = "Survey Closed"), {Survey Tracker Range 1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 12, 31)))

Any help is appreciated. Thanks!

Paul Newcome

You changed the syntax inside of the CONTAINS function when you combined them. The first formula has the right syntax for the CONTAINS function.

Paul Newcome

@Kerry St. Thomas You missed the comma inside of the CONTAINS function between the value to search for and the range.

Paul Newcome

You changed the syntax inside of the CONTAINS function when you combined them. The first formula has the right syntax for the CONTAINS function.

Kerry St. Thomas

I focused on the CONTAINS clause you used. If you're looking for things that say EXACTLY "Survey Closed" (so it wouldn't count "Survey Closed Tomorrow" or something like that), you can just say "Survey Closed" in that whole bit to accomplish what you're after:

=COUNTIFS({Survey Tracker Range 2}, "Survey Closed", {Survey Tracker Range 1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 12, 31)))

This will address exact matches. If you DO need to count those instances where the cell includes approximate matches (the "Survey Closed Tomorrow" things) then you can use CONTAINS -

=COUNTIFS({Survey Tracker Range 2}, CONTAINS("Survey Closed",@cell), {Survey Tracker Range 1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 12, 31)))

Paul Newcome

@Kerry St. Thomas You missed the comma inside of the CONTAINS function between the value to search for and the range.

MikeMagruder

https://community.smartsheet.com/discussion/comment/444175#Comment_444175

That was it! Thank you. This now works:

=COUNTIFS({LOST Survey Tracker Range 2}, CONTAINS("Survey Closed", @cell), {LOST Survey Tracker (Consolidated) Range 1}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 12, 31)))

Paul Newcome

Happy to help. 👍️