COUNTIF until first space

**hooshies** · 07-28-2010, 10:27 AM

Hi all,

I have a column with the following format of data:

1- A,B,C,D
2- A,B - some text
3- C,E
4- A - more text

What I'm trying to do, is count the number of As, Bs, Cs, Ds, and Es in the column. However, I run into problems with cells like the 2nd row above, where the COUNTIF function with count 2 "E"s in row two.

I basically want the COUNTIF function to analyze the characters in the cell until the first space, so that a =COUNTIF(A1:A4,"*E*") would return a 1, instead of a 5 (the 4 E's in rows 2 and 4 from the rest of the text)

I DO NOT want to create another row and TRIM the cells at the first space and then COUNTIF on that...I was hoping there was a better solution.

Thanks for your help.

**DonkeyOte** · 07-28-2010, 10:32 AM

Would the below work for you ?

=SUMPRODUCT(--ISNUMBER(SEARCH(",E,",SUBSTITUTE(","&A1:A4&","," ",","))))

that said it's not clear what should happen should you have multiple E references in a given cell - eg A,E,E ... is this one / two or perhaps not even possible ?

**hooshies** · 07-28-2010, 10:42 AM

Thanks DonkeyOte, that worked great!

The cells never have multiple character references (until the space and the text strings).

Would be asking too much for you to explain how that formula actually works, and what each function is doing?

**DonkeyOte** · 07-28-2010, 10:59 AM

Best to break into component parts

Please Login or Register  to view this content.

normalises the strings - removes spaces and replaces with commas and adds a comma prefix and suffix to each string - eg:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

With each term delimited by a comma the next thing is to see if the value of interest (encased within delimiter) appears within the string

Please Login or Register  to view this content.

If ,E, is found the SEARCH will return a number - if not it will return an Error .. the ISNUMBER will thus return TRUE (found) or FALSE (not found).

The double unary preceding the ISNUMBER test will in turn coerce the Boolean result to Integer equivalent - TRUE equating to 1 and FALSE 0.

The SUMPRODUCT then sums the 1/0 outputs - one output for each string referenced.