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

1. ={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"))}

2. ={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C9,"")))/LEN(C9))}

3. ={SUM(LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER(C9),"")))/LEN(C9))}

### 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

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

2. ={(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C9,"")))/LEN(C9))}

• ArrayFormula use "Ctrl+Shift+Enter"

• criteria as a cell reference

• criteria = case sensitive

3. ={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

1. =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

2. =ArrayFormula(SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,C7,"")))/LEN(C7))

• criteria as a cell reference

• criteria = case sensitive

3. =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