Contains Formula

Hello, I am trying to make a CountIfs formula that counts across a different spreadsheet, on if the comments cell contains the words "Moved", "Delayed", "Rescheduled", "Canceled", then output the number of these delayed cells.

Having some trouble getting this to work as I think an OR statement is nested somewhere in here.

=COUNTIFS(AND({Domain}, [Domain & Area]@row, OR(CONTAINS("Canceled", {Comments}) CONTAINS("Delayed", {Comments}), CONTAINS("Moved", {Comments}), CONTAINS("Rescheduled", {Comments}))))

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@RingJake I'm assuming you are referring to a column called "Comments" and not trying to search the Smartsheet comments/conversations on a row, right?

My initial thoughts: You're missing a comma (where the red circle is.) You also don't really need the AND because COUNTIFS will only count what matches all the conditions; AND is basically built in. You only really need AND to specify multiple logical statements for an IF, or to use to group criteria inside an OR.

So your logic can say:

Count if the Domain column or [Domain & Area] from this row, and also matches at least one of the criteria from within this OR statement.

=COUNTIFS({Domain}, [Domain & Area]@row, OR(CONTAINS("Canceled", {Comments}), CONTAINS("Delayed", {Comments}), CONTAINS("Moved", {Comments}), CONTAINS("Rescheduled", {Comments})))

(As always, make sure the first open parentheses color matches the last end parentheses color.)

RingJake

Thanks Jeff, and you are correct where Domain is from another sheet. This makes a little more sense - however the cell is outputting "Incorrect Output". Any other suggestions?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326330#Comment_326330

That's very odd. "Incorrect Output" is not even listed as a formula error message in Smartsheet.

Your {Domain} and {Comments} ranges are in the same remote sheet, correct?

Your {Comments} column - what is the column type (text/Number, dropdown, multi-select dropdown?)

Can you share a screenshot of your data structure and the error message?

Paul Newcome

@Jeff Reisman We keep bumping into each other today. Haha.

"Incorrect Output" is definitely something I have never seen before. I am leaning towards maybe it is more of an "Incorrect Argument Set" type of issue because the syntax looks a little off. We don't have range, criteria, range, criteria going. This should work:

=COUNTIFS({Domain}, [Domain & Area]@row, {Comments}, OR(CONTAINS("Canceled", @cell), CONTAINS("Delayed", @cell), CONTAINS("Moved", @cell), CONTAINS("Rescheduled", @cell)))

Jeff Reisman

@Paul Newcome Yep

I'm also trying to determine if CONTAINS is even necessary here. If the comments cells just contain one word to match, or if it's a multi-select select, something different might work better.

RingJake

Hi Jeff,

Thank you for the quick replies here - as requested please find two screenshots of my data structure.

Screen Shot 2022-04-15 at 11.49.41 AM.png

Screen Shot 2022-04-15 at 11.52.42 AM 1.png

Jeff Reisman

@RingJake

Update your Chrome (out-of-date Chrome can many times cause Smartsheet issues) and then try Paul's version of your formula.

RingJake

Thanks @Jeff Reisman & @Paul Newcome, I've updated my Chrome and rebooted my computer as well. When inputting Paul's formula I am getting "Incorrect Argument". CONTAINS and OR statements are always a bit tricky.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326362#Comment_326362

Incorrect Argument Set means there's either an issue with the range sizes (ex. selecting an entire column for one range in the formula, and selecting a subset of rows for another range,) or with missing an argument in a function.

Make sure your {Domain} range and {Comments} range both are referencing the entire columns, or the same subset of rows in their columns. Double-check that parentheses color-coding in the formula matches up as well.

RingJake

Ah, that was it - thank you guys and have a great weekend!

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.