COUNTIF Function

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:

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.