COUNTIF Function

ejacksonBPT

Hello,

How do I calculate the number of "completed" tasks in Phase 1 only?

I figured out how to calculate the number of "completed" for my whole sheet but if I copy that formula to a new column in my metric sheet and highlight just the status column cells in Phase 1 then my whole sheet value also changes to that.

This is the formula for the whole sheet "completed" value I'm using that is working correctly:

This is the formula I tried to use to calculate just Phase 1 and the value is 0 which is not correct, should be 3:

Here is my sample grid:

Protonsponge

Hello @ejacksonBPT

Could you have a helper column in your data source sheet with the formula =PARENT([Task Name]@row)

This will give you the parent task name on each row which you could use in a COUNTIFS formula in your metric sheet.

Data Sheet -

In your metric sheet, you could then use COUNTIFS referencing the helper column in the formula.

=COUNTIFS({Data Sheet_Status Column}, [Primary Column]@row, {Data Sheet_Helper-Parent Task Name}, "Phase 1")

Mertic Sheet -

I hope that is helpful to you in someway,

Protonsponge

ejacksonBPT

Hello,

Thanks for answering. I must be doing something wrong because it's not working, showing #UNPARSEABLE.

"TEST Range 1" I highlighted the Status column and "TEST Range 2" I highlighted the Helper column

Protonsponge

Hello @ejacksonBPT, it looks like you have an extra set of curly brackets {} in your formula. If you remove 1 set from each of your cross sheet references, I believe you should be good.

=COUNTSIFS({TEST Range 1}, [Primary Column]@row, {TEST Range 2}, “Phase 1”)

Protonsponge