Forum Discussion
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 the word "Apache" appears in the "Services Effected" Column and the be able to sort by "denXXXX" server in a second column.
2 Replies
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.
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!
