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.

 

[beefasaurus]

Obviously it needs a specific range like

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

What result do you get, error or wrong count?

Yes, I have entered the named range in both areas. I get #VALUE error.

The formula I am entering is =SUMPRODUCT(ISERR(FIND(" ",shows))*shows<>"")

 

[MrKowz]

Try:

=SUMPRODUCT(--(ISERR(FIND(" ",shows))),--(shows<>""))

 

[DonkeyOte]

@beefasaurus, if you compare your version above to Barry Houdini's you'll note some missing parentheses, ie:

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

Regards the two COUNTIFs suggestion

=SUM(COUNTIF(shows,{"*","* *"})*{1,-1})

or if using XL2007+

=COUNTIFS(shows,"*",shows,"<>* *")

I guess I'd argue the COUNTIF(S) approach to be the more robust but others will no doubt prove otherwise