Home

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

LuxRenata
Occasional Visitor

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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies