Creating a report using COUNTIFS with multiple fields and date range
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!
Comments
-
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.
-
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(...))
-
Good catch Paul! I just wanted to chime in and offer some help articles for future reference:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!