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))}

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.