Count IF function not working

Hi,

I am trying to create a count if function that will count all the cells that include text in a row, but not if that text is "NONE" or "CURRENT REQ"

I've gotten this far, =COUNTIFS(Taleo11:[Media 5 Request]11, OR(@cell = "NONE", @cell = "CURRENT REQ")) but the formula keeps returning 0 no matter what is in the row.

Can anyone tell me what I am doing wrong? I'm thoroughly confused.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Samuel Mueller

@STice326 try this then,

=COUNTIFS(Taleo11:[Media 5 Request]11, not(contains("none", @cell)), Taleo11:[Media 5 Request]11, not(contains("current req", @cell)), Taleo11:[Media 5 Request]11, ISTEXT(@cell))

All comments

Samuel Mueller

@STice326 that formula is counting only if it's none or current req

Try

=COUNTIFS(Taleo11:[Media 5 Request]11, OR(@cell <> "NONE", @cell <> "CURRENT REQ"))

=COUNTIFS(Taleo11:[Media 5 Request]11, <> "NONE", Taleo11:[Media 5 Request]11, <> "CURRENT REQ"))

STice326

Thanks for trying, but it still doesn't seem to be working, it IS counting now so I'm not getting a 0 in return, but it isn't excluding "NONE" and "CURRENT REQ" from the count for whatever reason.

And now it is also counting the dates in the range, which also doesn't help.

For context, here's a small snippet of my datasheet:

So, my problem is I created this function for our sheet, =COUNTIF(Taleo15:[Media 5 Request]15, ISTEXT(@cell)) and that counts all the cells that are text only. And the point of that on my sheet is that we are trying to count the action items that we have completed on each row.

If the row starts with "Open and Post" then this formula works great, it counts the action items correctly. =COUNTIF(Taleo15:[Media 5 Request]15, ISTEXT(@cell))

But then one of my coworkers pointed out that NONE is being counted and it's not a task, and that if it says "CURRENT REQ" in that first column it was also being counted and it doesn't count as a task for us.

So the goal here was to edit the original function to exclude those two modifiers, and I'm now realizing that Current Req isn't in caps so that could be part of it, but that's what I am trying to accomplish. In Excel, I can use the "*" wildcard symbol to get it to count the correct cells and not the dates, but I'm not seeing a way to accomplish that in Smartsheet.

I hope that context sort of helps.

Samuel Mueller

@STice326 try this then,

=COUNTIFS(Taleo11:[Media 5 Request]11, not(contains("none", @cell)), Taleo11:[Media 5 Request]11, not(contains("current req", @cell)), Taleo11:[Media 5 Request]11, ISTEXT(@cell))

STice326

That works! Thank you so much

Samuel Mueller

Awesome!

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.