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

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:

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!