# Creating a report using COUNTIFS with multiple fields and date range

Options
edited 12/09/19

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:

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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(...))

• Employee
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!