Creating a report using COUNTIFS with multiple fields and date range

spechj
spechj
edited 12/09/19 in Formulas and Functions

Hi:

I have a schedule Smartsheet that I am attempting to build a report via COUNTIFS formulas which pulls data from the schedule smart sheet.

My schedule smartsheert (source data) has the following fields & Column Values, I'm using to attempt this report:

Individual Names (Text), Status (Text), Start Date (Date), End Date (Date)

I have the following formula:

  =COUNTIFS({2019 H1 NAMER Schedule - Instructor}, "NAME", {2019 H1 NAMER Schedule - Activity H1}, "FREE", {2019 H1 NAMER Schedule - Start Date}, >=DATE(2019, 1, 1), {2019 H1 NAMER Schedule - Start Date}, <=DATE(2019, 12, 31)+COUNTIFS({2019 H1 NAMER Schedule - Instructor}, "NAME", {2019 H1 NAMER Schedule - Activity H1}, "FREE", {2019 H1 NAMER Schedule - Start Date}, >=DATE(2019, 1, 1), {2019 H1 NAMER Schedule - Start Date}, <=DATE(2019, 12, 31)))

This is intended to count how many times a person's status = "Free" for each date range. The formula above works without the addition of the second COUNTIFS. So the following formula works correctly:

=COUNTIFS({2019 H1 NAMER Schedule - Instructor}, "NAME", {2019 H1 NAMER Schedule - Activity H1}, "FREE", {2019 H1 NAMER Schedule - Start Date}, >=DATE(2019, 1, 1), {2019 H1 NAMER Schedule - Start Date}, <=DATE(2019, 12, 31)

However, when I add the + and rest of formula I get an "incorrect operation" error. I have used + with multiple COUNTIFS before, but am stuck on this one. Seems, the date range is throwing error.

Thanks!

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!