Using CountIfs with several criteria including names, status, and date range

Alex DelGiorno

I've created a sheet to build metrics from a very large source sheet. I'm trying to count based on the following:

1. the column {Module 3 - Section Owner} matches a cell in this metric sheet in the "Primary Column"
2. a Date in the {End Date} that is between Today() and the next 7 days. I created three cells that determine what is a) Today, b) Time to Add (7 days), and c) end date of range, which is a + b.
3. {Module 3 - Status} matches the header in the table.

All the {} are the formula ranges from the full column I've set to reference the source sheet for the.

Here is what the table looks like:

And here is the current formula I've been trying:

=COUNTIFS({Module 3 - Section Owner}, $[Primary Column]@row, {End Date}, >= $Date$1, {End Date}, <= $Date$81), {Module 3 - Status}, [Column3]$72)

Here is an screen shot of the columns in the source sheet:

Jeff Reisman

Try this:

=COUNTIFS({Module 3 - Section Owner}, $[Primary Column]@row, {End Date}, AND(@cell >= $Date$1, @cell <= $Date$81), {Module 3 - Status}, [Column3]$72)

Jeff Reisman

Try this:

=COUNTIFS({Module 3 - Section Owner}, $[Primary Column]@row, {End Date}, AND(@cell >= $Date$1, @cell <= $Date$81), {Module 3 - Status}, [Column3]$72)

Alex DelGiorno

It worked! Thank you so much!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320125#Comment_320125

Great! Sometimes Smartsheet functions can act strangely when the same range is listed twice in a formula. Using the AND function or the OR function to evaluate a range for two criteria simultaneously often helps.