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


  • John Jonassen
    John Jonassen ✭✭✭✭

    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.

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!