COUNTIFS functionality for multiple entries

Noelle Nordstrom

New to SmartSheet and I promise I have been looking around for an answer, but without any luck so I thought I would throw it out to the community...

I am looking to sum up all entries in column in a summary total at the top of my sheet, but the formula I have is only calculating where the searched for text is alone in the cell, not where it's combined with other text.

My end goal is to sum up the number of times each list is used, so maybe I have the complete wrong formula entirely.

This is the formula that I am currently using =COUNTIF([Marketing List]:[Marketing List], "NY_Office Leasing")

Thanks for your time!

Andrée Starå

Hi Noelle,

Try something like this.

=COUNTIF([Marketing List]:[Marketing List]; CONTAINS("NY_Office Leasing"; @cell))

The same version but with the below changes for your and others convenience.

=COUNTIF([Marketing List]:[Marketing List], CONTAINS("NY_Office Leasing", @cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Noelle Nordstrom

Andrée, Thank you very much.

That formula DID work, but only if I changed the column from a Multi-Select Drop down to a Text/Number. Is that the only way this works? Maybe I have my sheet set up incorrectly?

Andrée Starå

Happy to help!

It should work work with the Multi-Select as well. Check so the text is exactly the same for what you want to count.

Noelle Nordstrom

Ok, I think it's working properly now. Thank you very much Andrée. LIFESAVER, lol!

It looks like if I put the formula in the cells directly above my column of data and I try to use the formula to count each data set the formula registers as a circular reference.

I guess there is no way to "divorce" the top "summary" rows from the data in the column?

Andrée Starå

Excellent!

Happy to help!

When I read lifesaver, I thought. Haha!

https://media.giphy.com/media/yI73Iv1vLqJCo/giphy.gif

To make it work with the top summary you would need to define the range to exclude it. Would that work?

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You could indent all of the rows beneath the blue row and then specify your range to be CHILDREN([Column Name]7) referencing the cell in the parent row which would be the blue bar row.

You could also include these formulas in Sheet Summary Fields if needed.

Noelle Nordstrom

Sadly we do not have access to Sheet Summary. I am hoping to upgrade soon (fingers crossed).Yes, I would love to define the range and/or specify the range to be children so that the top summary rows aren't included in my totals, but my formula doesn't seem to work...

This is the formula that is counting everything, including the top summary row.

COUNTIF([Marketing List]:[Marketing List], CONTAINS("NY_Office Leasing", @cell))

This the formula that i tried to write indicating only count the CHILDREN of column Marketing List starting at row 7 (the blue parent row)

COUNTIF(CHILDREN([Marketing List]7, CONTAINS("NY_Office Leasing", @cell))

Paul Newcome

You just need to close off the CHILDREN function is all.

=COUNTIF(CHILDREN([Marketing List]7), CONTAINS("NY_Office Leasing", @cell))

Andrea Sentali

Hi all,

Sorry to bring this up again but I still have some issues to pull one user from a referenced column on a sheet {Assigned To 2}:

=COUNTIFS({Assigned To 2}, Primary12, {Status 2}, <>"Complete") same if I use =COUNTIFS({Assigned To 2}, CONTAINS(Primary12, @cell), {Status 2}, <>"Complete")

If the has multiple names I cannot detect one of the user... Thanks again for rescuing me :)

Paul Newcome

@Andrea Sentali The CONTAINS function doesn't much care for contact type columns. Try a FIND function instead:

=COUNTIFS({Assigned To 2}, FIND(Primary12, @cell) > 0, {Status 2}, <>"Complete")

Andrea Sentali

@Paul Newcome , thank you Paul, worked like a charm :)

Paul Newcome

@Andrea Sentali Happy to help. 👍️

Indy Chohan

@Paul Newcome - I'm new to smartsheets, and struggling w/ a similar issue. I have a column w/ assigned names (some are single names, other are multiple) - the column is set up as multiple entry (checkboxes).

I am trying to tabulate the occurrence associated w/ a name via a reference file (not a big deal), where I have two columns, "Category" in this case name, and then in the "cell" the formula below.

COUNTIF({Request Tracker Range 3}, CONTAINS(@Category@row; @cell))

I get #UNPARSABLE

Category Cell

Name1 formula

Name2 formula

etc

What am I missing? Appreciate the help!

Indy Chohan

https://community.smartsheet.com/discussion/comment/292470#Comment_292470

figured it out - COUNTIFS({Request Tracker Range 3}, HAS(@cell, Category@row))

Paul Newcome

That is correct. There are a couple of issues with your original formula. The error itself was coming from the semi-colon inside of the CONTAINS function instead of a comma. Even fixing that though, the CONTAINS function does not like contact type column data, so you would have had to switch to a HAS or FIND function anyway.