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
Love this!
ReplyDeleteSo good..
ReplyDeleteThank you.....