Count the number of occurrences of a text string in a range in Excel/Sheets
={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"))}
={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C9,"")))/LEN(C9))}
={SUM(LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER(C9),"")))/LEN(C9))}
Use 3rd formula to make the criteria non-case sensitive
ArrayFormula use "Ctrl+Shift+Enter" for all three formulas
A2:A7 = Ranges; C9 and Apple = criteria
Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.Count the number of occurrences of a text string in a range in Excel
How to count the number of occurrences of a text string in a range in Excel?
COUNT THE NUMBER OF OCCURRENCES OF A TEXT STRING IN A RANGE — EXCEL FORMULA AND EXAMPLE
={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"))}
ArrayFormula use "Ctrl+Shift+Enter"
criteria within the formula and must be inside the quotation mark
criteria = case sensitive
={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C9,"")))/LEN(C9))}
ArrayFormula use "Ctrl+Shift+Enter"
criteria as a cell reference
criteria = case sensitive
={SUM(LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER(C9),"")))/LEN(C9))}
Use 3rd formula to make the criteria non-case sensitive
ArrayFormula use "Ctrl+Shift+Enter"
criteria as a cell reference
Count the number of occurrences of a text string in a range in Google Sheets
How to count the number of occurrences of a text string in a range in Google Sheets?
COUNT THE NUMBER OF OCCURRENCES OF A TEXT STRING IN A RANGE — GOOGLE SHEETS FORMULA AND EXAMPLE
=ArrayFormula(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"))
criteria within the formula and must be inside the quotation mark
criteria = case sensitive
=ArrayFormula(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C7,"")))/LEN(C7))
criteria as a cell reference
criteria = case sensitive
=ArrayFormula(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER(C7),"")))/LEN(C7))
Use 3rd formula to make the criteria non-case sensitive
criteria as a cell reference
criteria = non-case sensitive
- Count cells in the column that have the same first five characters
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- Count cells that contain a specific text and ignore blank
- Count cells that contain errors
- Count if cells start with a specific text
- Count if cells end with a specific text
- Count the number of words in a cell
- Count the number of cells that have more than 5 characters words
- Count words in a range of cells and columns
- COUNTIF based on multiple criteria
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two values
- Combine two or more cell
- Extract left before first space
- Extract the nth words in a text string
- Flip the first and last name
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Convert date to text
- Convert time to text
- Convert weekday string to number
- Combine two or more cells with a line break
- Combine two or more cells and transpose