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

Melissa Rojas

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")😀

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"))

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!