CODEWITHSUNDEEP
google-sheets
stax
stax

Reputation: 33

Count number of words in range of cells separated by "-" in google sheets

i'm having some issues with a formula to count number of words in a range of cells separated by "-".

The formula that I have is working but if the cells are empty, it still shows "1" instead of "0".

Formula : =COUNTA(SPLIT(ARRAYFORMULA(CONCATENATE(D16:G16&CHAR(45)));CHAR(45)))

It also works for 2 ranges of cells but not 3. If anyone can help me out it'll be apreciated ! Thanks in advance!

Upvotes: 1

Views: 49

Answers (1)

General Grievance
General Grievance

Reputation: 4998

Your SPLIT is returning an error, which counts as a non-empty cell.

Add an IFERROR to handle the error. You can also use TEXTJOIN instead of CONCATENATE.

=COUNTA(IFERROR(SPLIT(ARRAYFORMULA(TEXTJOIN(CHAR(45),1,D16:G16)),CHAR(45))))

Upvotes: 2

Related Questions