COUNTIFS not working

Hello,

I have attempted to COUNTIFS from one smartsheet to another, however I have had no luck. Once smartsheet is being used to collect numbers and the other is getting information pulled in from Jira.

Screen Shot 2021-08-24 at 1.46.15 PM.png

Screen Shot 2021-08-24 at 1.46.28 PM.png

As you can see in the screenshots I am trying to count certain words in text fields and not a matching character in a cell, which I think is what is making this tricky. I have also tried combining formulas with FIND and HAS, but have has no luck.

Here is my current formula, which is not working, is somebody able to help me?

=COUNTIFS({Innovation Systems Impacted}, CONTAINS([Primary Column]@row, [Primary Column]:[Primary Column]), {Innovation Systems Impacted}, [Column2]1)

Find more posts tagged with

Formulas

Accepted answers

All comments

Tim Shaded

The way I understand this is that cells on the other sheet being referenced could contain a text string which would contain both of the words from the cells on the formula sheet (example: "ICB Complete"). In that case, both of the words would need to be referenced with a "CONTAINS" function for proper matching.

I haven't tested it, but you can try this:

=COUNTIFS({Innovation Systems Impacted}, CONTAINS([Column2]1, @cell)), {Innovation Systems Impacted}, CONTAINS([Primary Column]@row, @cell))

RingJake

Hey @Tim Shaded ,

Thanks for the reply - however when I enter this I receive UNPARSABLE despite the function seeming to align with everything.

I've been stuck on this for over a week.

Andrée Starå

Hi @RingJake

I hope you're well and safe!

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

https://community.smartsheet.com/discussion/comment/302587#Comment_302587

Hi @RingJake

Tim's formula is closer to what you'll want to do, however can you clarify what cross sheet reference {Innovation Systems Impacted} is looking at?

It sounds like you have two separate columns to search, one for "ICB" and one for the status, is that correct? If so, you'll need two separate cross-sheet references, one for each column.

A COUNTIFS works like this:

=COUNTIFS({First Column to Search}, "Criteria 1", {Second Column to Search}, "Criteria 2")

It looks like there's an extra closing parentheses after the first @cell in the formula above, so try something like this instead:

=COUNTIFS({Innovation Systems Impacted}, CONTAINS([Primary Column]@row, @cell), {Status Column in other sheet}, [Column2]$1)

Cheers,

Genevieve

RingJake

That worked, thank you @Genevieve Evans!

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.