Count IF formula that meets multiple criteria.

I am looking to get an accurate count of someone’s workload. I want to count if a specific person’s workload meets multiple stage criterion such as “Execution” and “Planning”

The Support Required column is a contact list. The Stage column in a dropdown list.

I am pulling this information into a separate sheet hence the {} for the cross sheet formula.

The below formula works and counts all projects they are listed on under “Support Required”

=COUNTIF({Master Sheet Support Required}, FIND("Denise", @cell) > 0)

When I try to add additional criteria it comes back at #incorrect agreement.

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, AND(IF({Master Sheet Stage} = "Execution")))

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@AlexisJ COUNTIFS will only count a row if all the criteria are present. So if you want to count multiple possible values in the same range, you need to incorporate an OR function to make that work.

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, OR(@cell = "Execution", @cell = "Planning"))

You can keep adding more Stage values in the same way within the OR if needed.

All comments

Jeff Reisman

@AlexisJ Just a syntax issue. In COUNTIFS and SUMIFS, since you can list multiple criteria ranges, the AND is implied and built into the function, plus you don't need the IF in there. The syntax is just COUNTIFS(range1, criteria1, range2, criteria2...) Try this:

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, "Execution")

If the above has an issue, maybe try:

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, @cell = "Execution")

AlexisJ

@Jeff Reisman That is helpful! I am now running into another issue. I hope you can help out.

It works if I am only looking to count 1 stage "Execution". When I try to add a second stage it doesn't count any of the projects and reports a 0.

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, @cell = "Execution", {Master Sheet Stage}, @cell = "Planning")

Jeff Reisman

@AlexisJ COUNTIFS will only count a row if all the criteria are present. So if you want to count multiple possible values in the same range, you need to incorporate an OR function to make that work.

=COUNTIFS({Master Sheet Support Required}, FIND("Denise", @cell) > 0, {Master Sheet Stage}, OR(@cell = "Execution", @cell = "Planning"))

You can keep adding more Stage values in the same way within the OR if needed.

AlexisJ

@Jeff Reisman Thank worked! Thank you so much.

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.