COUNTIFS with an OR formula

Deb White

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

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)

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

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)

Deb White

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