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

mleesc

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

mleesc

@Paul Newcome @Mark.poole

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

Mark.poole

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

mleesc

@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

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

mleesc

@Mark.poole

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

Mark.poole

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

Paul Newcome

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

@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?

Paul Newcome

@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

@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?

mleesc

@Paul Newcome @Mark.poole

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

Paul Newcome

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