Multi Criteria Formula

I need to create a formula that filters rows based on several criteria. The formula should show rows between specific date periods for the ‘Date Created’ and ‘Date Closed’ columns. For example, rows created between 01/01/23 and 02/01/23. Next, the formula should break those rows into counties based on the ‘County if known’ column. For example, rows that have county X, Y, etc. Finally, the formula should list the different status of the row based on the ‘Status’ column. For example, Assigned, Working, etc.

I want the final report to look like this:

I really appreciate assistance.

Tags:

• ✭✭✭✭✭✭

I would typically say a new thread would be in order since the original thread is a parsing / structure question and this newest one is syntax on a COUNTIFS, but we can just leave that as "for future reference".

There are a few reasons why that COUNTIFS is not working. First, you need a criteria for that first range (or remove the range entirely). Next, when using dates in a formula, you need to use the DATE function.

DATE(yyyy, mm, dd)

• ✭✭✭✭✭✭

What does your source data look like?

• Do you mean what are the column names or what does the data look like? Both?

• ✭✭✭✭✭✭

Both. Knowing what you want the end result to look like helps, but we also need to know what we are starting with as well.

• I'm going to come back to the question that started this post. But right now I need help with this formula. Should I start another post or can you help me here? It's "unparseable"

=COUNTIFS({complaints Range 1}, {complaints Range 2},>=(2023,01,01),{complaints Range 3}<=(2023,12,31))

Range 1

Range 2

Range 3

• ✭✭✭✭✭✭

I would typically say a new thread would be in order since the original thread is a parsing / structure question and this newest one is syntax on a COUNTIFS, but we can just leave that as "for future reference".

There are a few reasons why that COUNTIFS is not working. First, you need a criteria for that first range (or remove the range entirely). Next, when using dates in a formula, you need to use the DATE function.

DATE(yyyy, mm, dd)

• @Paul Newcome Thank you very much.

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!