COUNTIFS formula not functioning how I expect

Hello,

I am expecting this formula to count the number of cells in the "FND Prepare" column that have a certain name in them, where the cell on the same row in "Prepare Status" is not green.

=COUNTIFS([FND Prepare]:[FND Prepare], HAS(@cell, "NAME"), [Prepared Status]:[Prepared Status], (@cell <> "Green"))

Does someone have an idea as to where I could be going wrong? It is returning a number - 3 - but I am counting at least a dozen cells that have her name in it where the prepare status is not green.

Thank you for any help!

Find more posts tagged with

Sheets

Formulas

Sheet Summary

Accepted answers

AravindGP

Hi @PinnCason

It could be due to the blank values in Prepared Status. Try this formula.

=COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], OR(@cell <> "Green", @cell = ""))

All comments

AravindGP

Hi @PinnCason

When you write HAS in your formula, Smartsheet looks for an exact match. If NAME appears as part of another text, like First Name, Smartsheet will not pick this up. Switch the formula as below.

=COUNTIFS([FND Prepare]:[FND Prepare], CONTAINS("NAME", @cell), [Prepared Status]:[Prepared Status], (@cell <> "Green"))

=COUNTIFS([FND Prepare]:[FND Prepare], CONTAINS("NAME", @cell), [Prepared Status]:[Prepared Status], <> "Green")

PinnCason

Hey @AravindGP,

Thank you for your help. Unfortunately both formulas provided are returning an incorrect number - now 0. Please see screenshot attached for context. Is there anything else that could be going wrong here?

AravindGP

Hi @PinnCason

It looks like the column is a single select Contact List column. Since there could be only one value, you can try this formula to make it work.

=COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], <>"Green")

PinnCason

@AravindGP, the new formula is still returning a value of three, which per the screenshot is not accurate. You are correct that the name column is single select. Choosing "restrict column to list values only" does not solve my issue either.

Could the problem be somewhere in the way Smartsheet is interpreting the names that are input in that column?

Thank you,

AravindGP

Hi @PinnCason

It could be due to the blank values in Prepared Status. Try this formula.

=COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], OR(@cell <> "Green", @cell = ""))

PinnCason

Hey @AravindGP,

Looks like this is the correct solution. the formula was indeed not counting empty cells.

Thank you so much for your help!

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