Using CountM to return Count on Specific Items?

Gaeli · 03/23/20

In the CountM Article is states you can collect a total number however I would like a count of Specific Items. For example how many Pears? How many Apples? Is that possible?

Paul Newcome · 03/23/20

If you want to count how many times the word "Pears" shows up in a multi-select column whether it is the only selection or one of many within a cell, you can use the CONTAINS function within a COUNTIFS.

=COUNTIFS([Multi-select Column]:[Multi-Select Column], CONTAINS("Pears", @cell))

None RBT · 03/23/20

Sounds similar to something I do. I count all open or closed cases. I also count by department. I use countifs([column name]:[column name], "criteria"). See below my example.

=COUNTIFS([Case Status]:[Case Status], "Open")

Paul Newcome · 03/23/20

If you want to count how many times the word "Pears" shows up in a multi-select column whether it is the only selection or one of many within a cell, you can use the CONTAINS function within a COUNTIFS.

=COUNTIFS([Multi-select Column]:[Multi-Select Column], CONTAINS("Pears", @cell))

Gaeli · 03/23/20

Hi Paul

Thank you for the prompt response. Smartsheet Support also responded as fast as you have. I appreciate it.

Your suggestion did work and so do theirs.

=COUNTIF(Range:Range, HAS(@cell, "Value"))

In the above formula, the Range:Range will be the column in question with the desired values. In the last section of the formula is where you'll define the value, such as HR or IT. Each value will require its own formula, however the only item changing between formulas will be the value at the end.

I was hoping on my Metric sheet that @row would work also to prevent having to type the 13 departments in each row but this working is very helpful

Thanks again!

Lauren Severe · 01/20/21

Hello @Paul Newcome

I'm looking to do this formula to count items in a column with the condition of X appearing in a multi-select column. My formula troubleshooting keeps popping back as invalid or incorrect arguments. Does anyone have additional advice?

Context: Count # of Red Flags within the flag column IF "XX" appears in a multi-select range.

=COUNTIFS({Range 1}, 1, (CONTAINS("XX",{Range 2})) is the basic syntax but I have also included a screen shot of what I'm currently sitting with.

Any advice is greatly appreciated!

Paul Newcome · 01/20/21

@Lauren Severe Try this...

=COUNTIFS({Range 1}, 1, {Range 2}, CONTAINS("XX", @cell))

Lauren Severe · 01/20/21

@Paul Newcome You're the best.

Paul Newcome · 01/20/21

@Lauren Severe Happy to help. 👍️

Fazel Taslimi · 03/29/21

@Paul Newcome - Hi Paul, I tried this but I get 0 as an answer.

=COUNTIFS([Assigned To]:[Assigned To], CONTAINS("Fred", @cell))

The "Assigned To column" allows multiple contacts per cell.

Am I missing something here?

Paul Newcome · 03/31/21

@Fazel Taslimi Try using a FIND function instead when working with contact type columns.

=COUNTIFS([Assigned To]:[Assigned To], FIND("Fred", @cell) > 0)

Formulas and Functions

Formulas and Functions

Using CountM to return Count on Specific Items?

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary