CountIF with multiple values in the same cell (same sheet)

Hello,

I'm using a CountIf formula in Sheets Summary to count how many times a project manager (using the contact list for my column) is assigned in this year's strategic initiatives. I was able to get it to work with one person per cell, however, I can't figure it out when their's multiple people per cell.

Current formula: =COUNTIF([Project Manager]:[Project Manager], "Name")😀

Find more posts tagged with

Accepted answers

Paul Newcome

The CONTAINS function would require a "range".

=countif([Project Manager]:[Project Manager], contains("name", @cell))

The problem with this though is that the CONTAINS function does not like contact columns. You would have to switch over to either a FIND or a HAS function.

=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))

All comments

hollyconradsmith

Maybe try: =countif([Project Manager]:[Project Manager], contains("name"))

Melissa Rojas

@hollyconradsmith I'm getting an "INCORRECT ARGUMENT SET" error

Paul Newcome

The CONTAINS function would require a "range".

=countif([Project Manager]:[Project Manager], contains("name", @cell))

The problem with this though is that the CONTAINS function does not like contact columns. You would have to switch over to either a FIND or a HAS function.

=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))

Melissa Rojas

@Paul Newcome That worked! Thank you.

Paul Newcome

Happy to help. 👍️

KC3001

@Paul Newcome You have some great ideas. I am stuck on mine as well and would love any help you can offer! I am compiling survey results and we are assigning a ranking category for each comment. The issue is, some comments are long and have multiple categories. When I go to total the ranking categories per location and the grand total for all locations, it's not pulling the cells with multiple comments.

I read through your comments where you helped others. When you put @cell in your formula, is that the range of cells? I am newer to smartsheet and need some practice and help!!

Example below:

Paul Newcome

@KC3001 When I use "@cell" in a formula, it should be entered exactly as is. Basically it tells the function to evaluate the previously established range on a cell by cell basis.

=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))

This basically says to evaluate the [Project Manager] column and count where each individual cell has "Name".

=COUNTIFS([Project Manager]:[Project Manager], HAS(individual cell, "Name"))

KC3001

Ok, thank you for your help. I think I'm still confused since it is giving me an error. I'll keep trying!

KimTDSYN

I'm trying to do the same - count something even if something else is in the cell with it.

=COUNTIFS({gapfunctional}, [Functional area]@row ) (used for the "count1" column

=COUNTIFS({gapfunctional}, [Functional area]@row , {gapPriority}, "Low") (used in each of the priority columns)

My attempts to use "contains" or "has" is not working. I don't know why the equation won't count even for one value (comes from dropdown box that allows for multiple selection).

KimTDSYN

I got it working:

=COUNTIFS({gapfunctional}, CONTAINS([Functional area]@row , @cell ))

=COUNTIFS({gapfunctional}, CONTAINS([Functional area]@row , @cell ), {gapPriority}, "Low")

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.