Countif cells that contain only one word

[beefasaurus]

Hello,

I am trying to come up with a way to create a formula that counts if any cell in a range contains one word. Is there a wildcard than can be used with countif that would allow me to specify a word count? Right now I am using =COUNTIF(range,"*"), which is the wrong wildcard. Cells with multiple words would not be counted.

Thanks in advance.

 

[Dannyh1]

Hi

how about in another column try

=IF(ISERROR(FIND(" ",A1)),"",1)

Then sum that column

 

[MrKowz]

Welcome to the forums!

Assuming words are separated with a space, we can handle this with a SUMPRODUCT:

=SUMPRODUCT(--(ISERROR(SEARCH(" ",range))))

 

[VoG]

Hi. One way

=SUMPRODUCT(--(LEN(A1:A4)=LEN(SUBSTITUTE(A1:A4," ",""))))

 

[texasalynn]

will this work

Excel Workbook
	B	E
2	abc 123	TRUE
3	abc	FALSE
Sheet3

Excel 2003

Cell Formulas
Range	Formula
E2	=ISNUMBER(FIND("|",SUBSTITUTE(B2," ","|")))
E3	=ISNUMBER(FIND("|",SUBSTITUTE(B3," ","|")))

then you could do a count of the True

 

[barry houdini]

To avoid counting blank cells.....

=SUMPRODUCT(ISERR(FIND(" ",range))*(range<>""))

 

[beefasaurus]

Welcome to the forums!

Assuming words are separated with a space, we can handle this with a SUMPRODUCT:

=SUMPRODUCT(--(ISERROR(SEARCH(" ",range))))

Thanks very much, this works great! Much appreciated!

 

[beefasaurus]

To avoid counting blank cells.....

=SUMPRODUCT(ISERR(FIND(" ",range))*(range<>""))

You are correct about the other solutions counting blank cells, however I can't quite get this solution to work. Thanks very much for the help!

 

[barry houdini]

Obviously it needs a specific range like

=SUMPRODUCT(ISERR(FIND(" ",A1:A10))*(A1:A10<>""))

What result do you get, error or wrong count?

 

[DonkeyOte]

Another possibility might be:

=SUM(COUNTIF(A1:A10,{"*","* *"})*{1,-1})

this would also disregard entries other than text