Creating a report using COUNTIFS with multiple fields and date range

spechj

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!

Andrée Starå

Hi,

Can you share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

You are putting a closing parenthesis in the wrong place. You should have 2 closing parenthesis before the +. One to close the DATE function and another to close the first COUNTIFS. You have three at the end of the overall formula when you should only have two (close the DATE and second COUNTIFS functions).

 

You have

 

=COUNTIFS(...........DATE(...) + COUNTIFS(............DATE(...)))

 

It should be

 

=COUNTIFS(...........DATE(...)) + COUNTIFS(............DATE(...))

Alejandra

Good catch Paul! I just wanted to chime in and offer some help articles for future reference:

• Formula Errors: https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation
• Formula Operators: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet