COUNTIFS a start date is less than today, and is an ancestor level 3
I am currently struggling with the following COUNTIFS formula
=COUNTIFS(AND([Start Date]:[Start Date], <= TODAY(), ISDATE([Start Date]:[Start Date]), [% Complete]:[% Complete] <= 0.5, [Helper Column]:[Helper Column] = 3))
It should read as follows...
Count a row if it...
has a Start Date that is less than or equal to today
and
That start date cell has a date in it
and
The helper column (which counts ancestors) is an ancestor level 3
However I just get an unparseable or incorrect argument set and I'm not sure why. I've used different variations of this formula with success in other cells so I'm not sure what I'm doing wrong.
for example in a cell that turns a flag on or off this works: =IF(AND([Start Date]3 < TODAY(), ISDATE([Start Date]3), [% Complete]3 < 0.5, [Helper Column]3 = 3), 1, 0)
Comments
-
With COUNTIFS, you don’t need to use AND, just a set of ranges and conditions separated by commas. Try this:
=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [Start Date]:[Start Date], ISDATE(@cell), [% Complete]:[% Complete], <=0.5, [Helper Column]:[Helper Column], =3)
-
Thanks Brian that worked like a charm! I appreciate you not just providing the correct formula but explaining why the one I was using was incorrect.
-
Happy to help. I'm glad it worked for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!