COUNTIFS CONTAINS plus DATE
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)))
Best Answer
-
Try this one:
=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell))
Cheers,
Sam-
primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!
Answers
-
I would actually create a report using filtering criteria and groupings. Have you tried that?
-
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))
-
I am using multi-option dropdowns, so it's not quite getting me what I need.
-
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/
Cheers,
Sam-
primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call! -
Thank you! I tried this and still got the #UNPARSEABLE error...
-
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".
-
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))
Cheers,
Sam-
primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call! -
Try this one:
=COUNTIFS({Start Dates}, >=DATE(2024, 2, 1), {End Dates}, <=DATE(2024, 2, 29), {Analysis Text}, CONTAINS("Analysis Needs Enhancement (Critical)", @cell))
Cheers,
Sam-
primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call! -
It worked! Thank you!
Thank you to everyone who responded, as well!
Help Article Resources
Categories
Check out the Formula Handbook template!
