COUNTIFS with an OR formula

I need to add up 3 different regions as well as 1 location within that region to see what deals have been closed in 2020. I am using this formula and it's coming back unparseable. It's due to the multiple regions I'm trying to pull in - I think.

=COUNTIFS([Business Unit]:[Business Unit], OR(@cell = "Region 1", @cell = "Region 2", @cell = "Region 3"), Location:Location "Orange County", Stage:Stage, "6 - Closed", YEAR:YEAR, 2020)

When I had the 1 region and not using the OR function, everything worked.

Thanks

DWhite

Find more posts tagged with

Accepted answers

L_123

Countifs criteria use AND functionality. You won't have any returns for regions 2 or 3 if you don't have orange county in them. Instead you need to stack your countifs statements to separate out region 1 for regions 2 and 3.

=COUNTIFS([Business Unit]:[Business Unit], @cell = "Region 1", Location:Location, "Orange County", Stage:Stage, "6 - Closed", YEAR:YEAR, 2020) + COUNTIFS([Business Unit]:[Business Unit], OR(@cell = "Region 2", @cell = "Region 3"), Stage:Stage, "6 - Closed", YEAR:YEAR, 2020)

All comments

L_123

=COUNTIFS([Business Unit]:[Business Unit], OR(@cell = "Region 1", @cell = "Region 2", @cell = "Region 3"), Location:Location, "Orange County", Stage:Stage, "6 - Closed", YEAR:YEAR, 2020)

you were missing a comma after location

Deb White

L@123 - question; Region 2 and 3 dont have an Orange County - will this affect my formula or still return a value for all. i,e, response for Region 1 - Orange County, Region 2, Region 3 with Stage 6 and Year 2020?

L_123

Deb White

L@123 - thank you for explaining this. This worked. Thank you!

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.