Forum Discussion

LuxRenata's avatar
LuxRenata
Copper Contributor
Feb 22, 2019

Help! I need excel to give me a record count if the cell contains certain words

Hey Everyone,   I need excel to tell me how many records contain a certain value in column using a formula, then be able to sort by another value.   For example: I need to know how many times th...
excel
Formulas and Functions
andyritzert's avatar
andyritzert
Brass Contributor
Feb 22, 2019

For part one of your question.... use =COUNTIF(services_affected, "*apache*") where 'services_affected' is a named range for the column (or just use A:A or A1:A9999, etc). The asterisk is a wildcard, so it will count 'apache' (not case-sensitive) no matter where it is in a cell.

For the 2nd part, if it's more complicated than using Sort/Filter on the Home ribbon then you'll have to send more information for what you want to do.

  • andyritzert's avatar
    andyritzert
    Brass Contributor
    Feb 22, 2019

    Also, if you want to count a list of words you'll need to concatenate the asterisks with the cell reference. For example, if Apache is in B1, Bill is B2, Cathy is B3, then the formula in C1 that counts 'apache' in column A would be:

    =COUNTIF( A:A, "*" & B1 & "*" )

    Then copy down thru the end of your word list.

    Hope that helps!

Resources