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!

Find more posts tagged with

Formulas

Accepted answers

John Jonassen

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.

All comments

John Jonassen

The formula that you have provided does successfully work for me. Are you able to provide a screenshot?

Jeannine Marquardt

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.

John Jonassen

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.

Jeannine Marquardt

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?

Cells with Formula from Other Sheets.PNG

Jeannine Marquardt

https://community.smartsheet.com/discussion/comment/262547#Comment_262547

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.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.