COUNTIFS CONTAINS plus DATE

ElizabethOwen

Hi there,

I am building out a quality control function. I have a list of findings that I would like to tally each month. So I would like, for the month of February, for my tally sheet to count the total number of items with "Analysis Needs Enhancement (Critical)" (for example) that is between the dates of February 1, 2024 and February 29, 2024.

I feel like I'm on the right track but... not there.

=COUNTIFS({Complete-CriticalErrors}, CONTAINS("Analysis Needs Enhancement (Critical)"), AND({DateItemCompleted}@cell, >= DATE(2024, 02, 01), {DateItemCompleted}@cell <= DATE(2024, 02, 28)))

Sam_Harwart

https://community.smartsheet.com/discussion/comment/427913#Comment_427913

Try this one:

=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell))

JSanita

I would actually create a report using filtering criteria and groupings. Have you tried that?

Carson Penticuff

You are definitely in the ballpark. Your CONTAINS function must contain both the content to search for and also the range to search. In this situation, you would substitute @cell for the range since the range is already designated in the function. There is also no need to use AND within the COUNTIFS since COUNTIFS is already designed to evaluate multiple functions. Give this a try:

=COUNTIFS({Complete-CriticalErrors}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell), {DateItemCompleted}@cell, >= DATE(2024, 02, 01), {DateItemCompleted}@cell, <= DATE(2024, 02, 28))

ElizabethOwen

https://community.smartsheet.com/discussion/comment/427900#Comment_427900

I am using multi-option dropdowns, so it's not quite getting me what I need.

Sam_Harwart

Hi,

Is the value you're looking for exactly "Analysis Needs Enhancement (Critical)"? A simpler way may be something like this:

=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, "Analysis Needs Enhancement (Critical)")

-

Sam | Prime Consulting | https://primeconsulting.com/

ElizabethOwen

https://community.smartsheet.com/discussion/comment/427901#Comment_427901

Thank you! I tried this and still got the #UNPARSEABLE error...

ElizabethOwen

https://community.smartsheet.com/discussion/comment/427904#Comment_427904

Almost! I am using multi-select dropdowns so I need it to be able to count if it contains the item. But I can't quite seem to figure out where to put the "CONTAINS".

Sam_Harwart

https://community.smartsheet.com/discussion/comment/427913#Comment_427913

Ok, try this one:

=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell))

Sam_Harwart

https://community.smartsheet.com/discussion/comment/427913#Comment_427913

Try this one:

=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell))

ElizabethOwen

https://community.smartsheet.com/discussion/comment/427923#Comment_427923

It worked! Thank you!

Thank you to everyone who responded, as well!