Suppose you are asked to count the number of cells in a range containing a particular word, example "hi" (without double quotes).
In Excel, the COUNTIF function is used to count the number of cells within a range that meet a specific condition. The condition is specified using a criteria, which can include wildcards.
Count Cells that Starts with a Specific Word
=COUNTIF(A2:A8,"Hi*")
This formula counts the number of cells in the range A2:A8 that start with the text "Hi". The asterisk (*) is a wildcard that represents any sequence of characters following "Hi". It will count cells with values like "Hi", "High", "Hiking", etc., but not cells with values like "helloHi" or "HolaHi".
Count Cells that Ends with a Specific Word
=COUNTIF(A2:A8,"*Hi")
This formula counts the number of cells in the range A2:A8 that end with the text "Hi". The asterisk (*) is a wildcard that represents any sequence of characters preceding "Hi". It will count cells with values like "Hi", "ThisIsHi", "BuyHi", etc., but not cells with values like "Hihello" or "HiHola".
Count Cells that Contains a Specific Word
=COUNTIF(A2:A8,"*Hi*")
This formula counts the number of cells in the range A2:A8 that contain the text "Hi" anywhere within their content. The asterisks (*) on both sides of "Hi" are wildcards that represent any sequence of characters before and after "Hi". It will count cells with values like "Hi", "ThisIsHi", "HiHola", "SayHiToEveryone", etc.
See the example shown in the image below.
The asterik (*) is used to find matches.
Download the workbook
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Love this!
ReplyDeleteSo good..
ReplyDeleteThank you.....