Count the number of cells that have more than 5 character words in a column

=SUMPRODUCT((LEN(A2)>5))

=COUNTIF(A3,REPT("?",5)&"*")

A2 & A3 = data cell

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

Count the number of cells that have more than 5 character words in a column in Excel

How to count the number of cells that have more than 5 character words in a column in Excel?
Count the number of cells that have more than 5 character words in a column in Excel

EXCEL FORMULA AND EXAMPLE

  1. =SUMPRODUCT((LEN(A2)>5))

  2. =COUNTIF(A3,REPT("?",5)&"*")

    • to count within a column =SUMPRODUCT((LEN(A:A)>5))

    • to count within multiple columns =COUNTIF(A:B,REPT("?",5)&"*")

Count the number of cells that have more than 5 character words in a column in Google Sheets

How to count the number of cells that have more than 5 character words in a column in Google Sheets?
Count the number of cells that have more than 5 character words in a column in Google Sheets

GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =SUMPRODUCT((LEN(A2)>5))

  2. =COUNTIF(A3,REPT("?",5)&"*")

    • to count within a column =SUMPRODUCT((LEN(A:A)>5))

    • to count within multiple columns =COUNTIF(A:B,REPT("?",5)&"*")

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