Add 2nd additional Criteria to Metric Formula (Open & Blank)

Sherry Fox

Okay I had a formula that was working perfectly, thanks to @Paul Newcome . And it was:

=COUNTIFS({Status}, @cell <> "", {Workstream Name}, @cell = "")

Now my boss wants to add "Open" from the status column. I tried to change it to:

=COUNTIFS({Status}, @cell <> "", {Workstream Name}, [Metric Name]@row, {Status}, ="Open")

But the result is 0. It should show a result of 2 in this cell

Paul Newcome

Your existing formula:

=COUNTIFS({Status}, @cell <> "", {Workstream Name}, @cell = "")

is counting rows where the status is not blank and the workstream is blank.

If you want to change it to look for Status = "Open" and Workstream = blank, it would look like this:

=COUNTIFS({Status}, @cell = "Open", {Workstream Name}, @cell = "")

Andrée Starå

Hi @Sherry Fox

I hope you're well and safe!

Wouldn't this work?

=COUNTIFS({Status}, @cell = "Open", {Workstream Name}, [Metric Name]@row)

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sherry Fox

@Andrée Starå ,

That doesn't work either. Remember this formula needs to count BOTH a Status that is Open and a Workstream that is a Blank Cell.

Andrée Starå

@Sherry Fox

Not sure I follow but try something like this.

=COUNTIFS({Status}, @cell = "Open", {Workstream Name}, OR(@cell = "", @cell = [Metric Name]@row))

Did that work?

Paul Newcome

Your existing formula:

=COUNTIFS({Status}, @cell <> "", {Workstream Name}, @cell = "")

is counting rows where the status is not blank and the workstream is blank.

If you want to change it to look for Status = "Open" and Workstream = blank, it would look like this:

=COUNTIFS({Status}, @cell = "Open", {Workstream Name}, @cell = "")

Sherry Fox

@Paul Newcome

Sorry, somehow I missed your post. This worked perfectly. I think this proves, don't Smartsheet without Coffee!!! Thanks for your help!

Paul Newcome

Happy to help. 👍️