I need to count the number of times a column contains specific text

Options

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

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    @mleesc

    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.

  • mleesc
    Options

    @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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

  • mleesc
    Options

    @Mark.poole

    count of each unique certification. So if 10 people selected A+ then the count for A+ =10

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Paul Newcome

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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).

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Paul Newcome

    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.

  • mleesc
    mleesc
    Answer ✓
    Options

    @Paul Newcome @Mark.poole

    Thank you both very much the =COUNTIFS({Cross Sheet Reference}, HAS(@cell, "A+")) works great

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!