I need to count the number of times a column contains specific text
I have a column that is a drop down where multiple selections can be made, and I need to count how many times each selection is selected in the column.
an example of the items in the drop down are:
A+
PenTest+
Project+
Server+
Security+
Tech+
CSIS
CIOS
I need to be able to count how many of each are selected. Formula of things I have tried are:
=COUNTIF({Certifications Range 4}, "A+, PenTest+, "Security+")
Best Answer
-
Thank you both very much the =COUNTIFS({Cross Sheet Reference}, HAS(@cell, "A+")) works great
Answers
-
Are you doing a separate column or cell for each possible Selection?
If so then you want.
=Countif({Certifications Range 4},Contains("A+",@cell))
=Countif({Certifications Range 4},Contains("PenTest+",@cell))
=Countif({Certifications Range 4},Contains("Project+",@cell))
So on and so forth.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole I have a column with a list of names a column for several certifying company columns for example Comptia, Cisco, EC-Council, … and each person selects all the certifications under each of the certifying company that they have on the row with their name. the example below is for the CompTia certifications
Name
ComTIA
Bob
Not Pursuing
Jeff
A+ Network+ Security+ CSIS CIOS
Jo
Not Pursuing
Chris
A+ Cloud+ Cloud Essentials+ CYSA+ ITF+ Network+ Project+ Security+ CSIS CIOS
Kevin
Not Pursuing
-
so your needing a total count of certifications are selected in the column for each name? Jo is 0 and Jeff is 5, Or a count of each unique certification. So if 10 people selected A+ then the count for A+ =10
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
count of each unique certification. So if 10 people selected A+ then the count for A+ =10
-
as there are several options you would want the original solution with the corresponding formula for each cert. how ever if you have a list of each cert on sheet to show the numbers for each cert you could use this formula to drag it down next to the list.
=Countif({Certifications Range 4},Contains(list@row,@cell))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
I would suggest a HAS function instead. The CONTAINS function looking for "A+" would count both "A+" and "CSYA+" which could lead to inaccurate counts. Using the HAS function means you will not get a count for "CSYA+" when trying to count for "A+".
=COUNTIFS({Cross Sheet Reference}, HAS(@cell, "A+"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
As always you come to the rescue. I’m still learning the ins and outs of all the functions. Has() is exact? I’m assuming?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole No worries. Yes. The HAS function looks for an exact selection within a multi-select dropdown or multi-select contact column (the CONTAINS function doesn't work at all on contact type columns).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
To be sure I'm on the right track with this one. Would you concur that either he has separate columns/ Cells for each possible option. with =COUNTIFS({Cross Sheet Reference}, HAS(@cell, "A+")) "A+" replaced with each option for each cell, Or to do a List column with =COUNTIFS({Cross Sheet Reference}, HAS(@cell, List@row)) in a column next to it?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thank you both very much the =COUNTIFS({Cross Sheet Reference}, HAS(@cell, "A+")) works great
-
@Mark.poole I always use the latter with a list and cell references, but I tend to leave that up to the user.
@mleesc Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!