Count the number of occurrences of a text string in a range in Excel/Sheets

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?

={(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

How to count the number of occurrences of a text string in a range in Google Sheets?

=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

Other useful "COUNT FUNCTIONS: TEXT BASED CRITERIA" formulas in Excel and Google Sheets

Others FormulasDate and time functions

Number based criteria

Extract functions

Google Sites

Report abuse