Help with COUNTIFS Formula

Nadia Gibbons

Hello Everyone! Happy Friday!

I am trying to create a Project Milestone Widget for my project dashboard. I want to be able to show the total number of tasks to be completed and the percentage that has been completed.

I used this formula to get the number of total tasks for each project phase (parent row). =COUNT(CHILDREN([Task Name]2))

I am having trouble with the last part. I am using this formula.

=COUNTIFS(CHILDREN([Task Name]2) [ Status:Status, "Complete”])

I was trying to mirror the formula I found on the Functions list

COUNTIFS

Advanced

COUNTIFS( range1 criterion1 [ range2​criterion2​... ] )

Re: https://help.smartsheet.com/functions

Is there any additional guidance anyone can give me?

Kelly Moore

Hey@Nadia Gibbons

You're right that the syntax for the COUNTIFS is (range1, criteria1, range2, criteria2, etc). In your formula above, the first range is the reference to the Children. The second part of this term will be the criteria - in this case you want the Children when the Status = Complete. From your formula, I'm not sure if there is any criteria associated with the [Task Name] column.

Try this and see how close the formula is. I'm happy to continue to help if we need to tweak it.

=COUNTIFS(CHILDREN(Status@row), "Complete")

If you need to call out Row#2 specifically, replace the @row with 2 . If you don't need that row specifically (there is something special about it), then use the @row designation instead. It makes a more robust formula for you as well as the sheet itself. There

cheers,

Kelly