RAID Metrics

I'm trying to figure out how to build a formula using the COUNTIFS function that will count the number of ACTION items in our raid log but only if those items don't have one of our multiple closed status's. ONLY Active items if you will.

Current Formula: =COUNTIFS({RAID Type}, "Action") - this returns the correct count of action items but it includes Action items that are also closed. We have multiple closed status's and I don't want those included in the count.

Example Raid Types - Action, Risk, Issue, Decision, Question

Example Closed Status - Closed - Decision, Closed - Resolved

Find more posts tagged with

Accepted answers

Jason Duryea

Sorry, the formula should be as shown below. I accidently used = sign instead of <>

=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell<>"Closed 1", @cell<>"Closed 2", @cell<>"Closed 3"))

Jason Duryea

No problem. Glad to help.

All comments

Jason Duryea

Use the OR() function within the COUNTIFS

=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell="Closed 1", @cell="Closed 2", @cell="Closed 3"))

You could also look at using =COUNT(COLLECT())

Tonya Baut

Let me try that, thank you very much!

Jason Duryea

Sorry, the formula should be as shown below. I accidently used = sign instead of <>

=COUNTIFS({RAID Type}, "Action", {Status}, OR(@cell<>"Closed 1", @cell<>"Closed 2", @cell<>"Closed 3"))

Tonya Baut

I'm getting an invalid reference error: =COUNTIFS({RAID Type}, "Action", {*Status}, OR(@cell = "Closed - Resolved", @cell = "Closed - Canceled", @cell = "Closed - Workday Case", @cell = "Closed - Decision", @cell = "Closed - Workday Brainstorm"))

Tonya Baut

Fixed it, the range reference was incorrect. Again, thank you.

Jason Duryea

Your {Status} reference has an asterisk in the reference name within the formula. Is that correct?

Tonya Baut

Well almost. The count is coming back as "1". there are actually 3 that are not closed.

Jason Duryea

Did you see my comment about changing the equal sign to <> ?

Tonya Baut

I think I figured it out. Tks

Jason Duryea

No problem. Glad to help.

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.