Getting Same Resutl with Slightly Different COUNTIF

Hi Guru's,

I run the below formula and it gives me the correct number however; I modify the formula to look at another range however; I get the same result as the first formula minus the 2nd range.

1st formula

=COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late") + COUNTIFS(Status:Status, "Not Started")

2nd formula

=COUNTIFS([Operational Priority]:[Operational Priority], "🔶", [Line of Business]:[Line of Business], "Provider Services", =COUNTIFS([Operational Priority]:[Operational Priority], ="1", Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late") + COUNTIFS(Status:Status, "Not Started")

Find more posts tagged with

Formulas

Accepted answers

NickBlocker

@Lucas Rayala I was able to fingure this solution out on my own using the @cell syntax. The formula that I ended with is below which I was able to validate using Excel piviot tables:

=COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", Status:Status, OR(@cell = "In Progress", @cell = "Not Started", @cell = "Pending Assignment", @cell = "Clarification", @cell = "Late", @cell = "Possible Risk"))

I do appreacte your help and attention to my questions.

All comments

Lucas Rayala

Your second formula has a formatting error in it at “=COUNTIFS([Operational Priority]:”

This lands in the middle of another COUNTIFS, in what is supposed to be a range.

NickBlocker

@Lucas Rayala Thank you for replying. After I corrected the formula and added in the "=" I am still encoutering issues. I get a result of 68 when it should be 39

In Progress = 5

Not Started = 8

Pending Assignment = 14

Adjusted Formula

=COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", [Line of Business]:[Line of Business], "Provider Services", Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late") + COUNTIFS(Status:Status, "Not Started")

I get the correct result using the below formula however; once you add in a "+ COUNTIFS" it gives you the incorrect result

=COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", [Line of Business]:[Line of Business], "Provider Services", Status:Status, "In Progress")

NickBlocker

@Lucas Rayala I was able to fingure this solution out on my own using the @cell syntax. The formula that I ended with is below which I was able to validate using Excel piviot tables:

I do appreacte your help and attention to my questions.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.