Is there an excel formula that can check the number of words in consecutive cells and give an output-CodePudding

I am trying to create an output in excel based off the number of words in cells. Essentially i want to check if the sum of the words in 3 cells is = 1,2 or >=3. Im using the len formula which i have successfully used on single cell conditions but im struggling to create the formula that would check multiple cells.

Below is an example of my data:

    Column A        Column B       Column C
    Cat;dog                         Bird

Formula

=SUMIF(AND(LEN(TRIM(A4))-LEN(SUBSTITUTE(B4," ","")) 1, LEN(TRIM(C4))-LEN(SUBSTITUTE(C4," ","")) 1, >=3), "Titanium")

First I made a single formula to work on a single cell. It ignores semicolons and commas to calculate total words. That formula is in column F and it's:

=IF(LEN(E5)=0;0;LEN(TRIM(SUBSTITUTE(SUBSTITUTE(E5;";";" ");",";" ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E5;";";" ");",";" "));" ";"")) 1

Notice I added an IF to make sure that blank cells will count as 0 words (because the 1 will be added wrongly and we need to avoid this.

Now you just need to sum up all results and we get 8 words.

What you want is to get this result with a single formula and that can be perfomed with array formulas. In cell F11 my formula is:

=SUM(IF(LEN(E5:E8)=0;0;LEN(TRIM(SUBSTITUTE(SUBSTITUTE(E5:E8;";";" ");",";" ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E5:E8;";";" ");",";" "));" ";"")) 1))

You need to introduce this formula pressing CTRL ENTER SHIFT or it won't work!

Now you got the result in a single formula and you just need to add your conditions mentioned in your post

UPDATE: In your Google Sheets, the correct formula would be:

=ArrayFormula(IF(SUM(IF(LEN(TRIM(A3:B3))=0,0,LEN(TRIM(A3:C3))-LEN(SUBSTITUTE(A3:C3," ","")) 1))>=3,"Good","Bad"))

Please, notice Excel is not the same as Google Sheets so sometimes the formulas may be different in one of them.