COUNTIFS Based on RYG with Multiple Criteria
A colleague and I are trying to create a sheet summary that counts each of "Red", "Yellow", and "Green" statuses. The formula we've tried to use for this is as follows:
=COUNTIFS(DMA:DMA, "St. Louis, MO", [2020 Reimage]:[2020 Reimage], "Y", [Ext. Paint]:[Ext. Paint], "Green")
However it returns either a #NO MATCH. The first criteria is that the demographic be St. Louis, MO, the second being our company's 2020 Reimage program column (as a Y or N), while trying to count stores whose painting process within those criteria is either in progress (Yellow), completed (Green), and on hold (Red). My colleague and I have checked that all values within the formula accurately match the column titles, so we're not sure how to fix it.
Any help is greatly appreciated!
Best Answer
-
Have you tried doing a countif on each range/criterion to see where there error lies? I would imagine that if there was a problem with them all together, at least one of them has an error in them on its own.
The only other thing I can think of would be to ask if you are using the correct Status Symbols to the criterion you are using, like maybe it isn't actually "Yellow" but "Hold". If that makes sense.
Take a peak at those two things...you'll get it figured out.
Answers
-
The formula that you have provided does successfully work for me. Are you able to provide a screenshot?
-
Unfortunately, no. The sheet has about 100 columns, and as a free collaborator I don't have the permissions to hide the columns I would need to in order to see all the data columns in question within one screen.
-
Have you tried doing a countif on each range/criterion to see where there error lies? I would imagine that if there was a problem with them all together, at least one of them has an error in them on its own.
The only other thing I can think of would be to ask if you are using the correct Status Symbols to the criterion you are using, like maybe it isn't actually "Yellow" but "Hold". If that makes sense.
Take a peak at those two things...you'll get it figured out.
-
Thank you! I'm going to check into those today, and should have an update by Monday morning. In addition, I've noticed some cells are pulling from another reference sheet, and some have populated 'No Match' within the cell in the Ext. Paint column. could this be effecting our outcome?
-
It is most certainly an error within the Ext. Paint Column, because the 2020 Reimage column counts up just fine. I'm hoping that getting those few 'No Match' values out of the Ext. Paint column will fix the issue, I just have to get the owner of the other sheet it pulls from to fix their formula within those cells or hardcode the RYG values instead.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 446 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
