Count cells that contain a specific text and ignore blank in Excel/Sheets

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

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

A2:A6 & A:A = Ranges; C6 = criteria

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

Count cells that contain a specific text and ignore blank in Excel

How to count cells that contain a specific text and ignore blank in Excel?
Count cells that contain a specific text and ignore blank in Excel

COUNT CELLS THAT CONTAIN A SPECIFIC TEXT AND IGNORE BLANK — EXCEL FORMULA AND EXAMPLE

💡 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 cells that contain a specific text and ignore blank in Google Sheets

How to count cells that contain a specific text and ignore blank in Google Sheets?
Count cells that contain a specific text and ignore blank in Google Sheets

COUNT CELLS THAT CONTAIN A SPECIFIC TEXT AND IGNORE BLANK — GOOGLE SHEETS FORMULA AND EXAMPLE

💡 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 FUNCTIONS — TEXT-BASED CRITERIAOthers FormulasDate and time functionsNumber based criteriaExtract functions