Count if cells contain a specific text in Excel and Google Sheets

=COUNTIF(A2:A7,"*apple*") // criteria within formula

=COUNTIF(A:A,"*"&C6&"*") // criteria as a cell reference

A2:A7 & A:A = ranges

Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.

Count if cells contain a specific text in Excel

How to count if cells contain a specific text in Excel?
Count if cells contain a specific text in Excel

COUNT IF CELLS CONTAIN A SPECIFIC TEXT — EXCEL FORMULA AND EXAMPLE

  1. =COUNTIF(A2:A7,"*apple*") // criteria within formula

  2. =COUNTIF(A:A,"*"&C6&"*") // criteria as a cell reference

  • A2:A6 = data range

  • "*apple*" = criteria

  • Wildcard: The * character allows for any number (including zero) of other characters to take its place.

💡 In this example, it’s used to find all cells that include the text "apple". This search is not case-sensitive, so "apple" is considered the same as "Apple" or "APPLE".

Count if cells contain a specific text in Google Sheets

How to count if cells contain specific text in Google Sheets?
Count if cells contain a specific text in Google Sheets

COUNT IF CELLS CONTAIN A SPECIFIC TEXT — GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =COUNTIF(A2:A7,"*apple*") // criteria within formula

  2. =COUNTIF(A:A,"*"&C6&"*") // criteria as a cell reference

  • A2:A6 = data range

  • "*apple*" = criteria

  • Wildcard: The * character allows for any number (including zero) of other characters to take its place.

💡 In this example, it’s used to find all cells that include the text "apple". This search is not case-sensitive, so "apple" is considered the same as "Apple" or "APPLE".

Other useful "COUNT FUNCTIONS: TEXT BASED CRITERIA" formulas in Excel and Google SheetsOthers FormulasDate and time functionsNumber based criteriaExtract functions