COUNTIFS a start date is less than today, and is an ancestor level 3

Options
tlawrence
tlawrence
edited 12/09/19 in Formulas and Functions

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!