Countif element of a cell is one of a few options

Hauger · 02/26/24

I am trying to find a formula that will return a count of how many elements of a single cell match my criteria. I am thinking some combination of a Countif, CountM... something? But I can't quite get it.

Here's what I'm trying to do specifically:

I have a contact list column that allows multiple people from my entire department to be entered. I want to be able to count how many people from only North America are included in that cell.

My sheet has a column header called "Additional Team Members" which is multiselect. I have a reference sheet with a column called "North American Team Members" that lists all the items I am interested in counting.

Is there a function or string of functions that basically combines COUNTM and COUNTIF (or some other formula) such that the logic is essentially "if an element in this cell is X or Y or Z, count it" or "Count how many times an element in this cell matches this reference list"

I'm not a SmartSheet expert, so I greatly appreciate any help! Thank you!

AravindGP · 02/27/24

Hi @Hauger

I believe you have the North American Team Members listed across multiple rows. If so, you can write a formula to get the count of each of them appearing in the main sheet and do a sum of that total to get the overall count. Your formula for each member would be something like =COUNTIF({Contact List column in source}, HAS(@cell, [North Amercian Team Members]@row))

The curly brackets represent a cross sheet reference, while the second part is to show the count against the individual's name.

Hauger · 02/27/24

https://community.smartsheet.com/discussion/comment/421948#Comment_421948

Hi! Thanks for taking the time!

I think this is not quite it, if I'm understanding correctly. Let me include an example of what I'm trying to do:

I'd like the "NA Count" column to count how many of my North America team members are listed under "Additional Team Members" of the same row, but NOT count anyone from Europe. I have a list of who is NA vs EU on a separate reference sheet

Dori and Erin are NA, so I would want the cells in the "NA Count" Column to return: 2, 2, 1 respectively.

I hope that makes sense. Again, I'm a relatively inexperienced SS user, but have worked a ton in Excel so I have a general grasp on how formulas work, but can't quite figure this one out.

I unfortunately can't do a sum of all the rows or all the instances in the sheet, because I need metrics related to each specific task.

Thank you!

AravindGP · 02/27/24

Hi @Hauger

Thanks for the details. How many NA members do you have. I have a solution which will get you the result you want, but it's not pretty. The longer version is a formula like this.

=IF(HAS([Additional Team Members]@row, {NA Team Member 1 reference}), 1) + IF(HAS([Additional Team Members]@row, {NA Team Member 2 reference}), 1) + IF(HAS([Additional Team Members]@row, {NA Team Member 3 reference}), 1) + ....

Note that you can only do up to 100 cross sheet references in a sheet. If you already have some references and have a lot of NA team members, it's not ideal. You can consider replacing the references with actual names in double quotes. Depending on the number of members, the formula could become quite big.

Hauger · 02/27/24

https://community.smartsheet.com/discussion/comment/422116#Comment_422116

Amazing - I will give it a try now! This seems very promising. There are only 11 people I'm interested in counting, so shouldn't be TOO cumbersome. I'll report back.

Thank you!

Hauger · 02/27/24

https://community.smartsheet.com/discussion/comment/422116#Comment_422116

GENIUS. It worked! Thanks so much for your help - am I am so appreciative :)

Countif element of a cell is one of a few options

Answers

Help Article Resources

Categories