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
Best Answer
-
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)
Answers
-
=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
-
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?
-
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 - thank you for explaining this. This worked. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!