COUNTIF on result of formula

[Joe M.]

I am trying to use COUNTIF to count the occurance of a text within a range of
cells which contains the results of a formula. Here is a simple example:

=COUNTIF(A1:A10,"HELLO")

A B
1 =LEFT(B1,FIND("X",B1)-1) HELLOXTHERE
2 =LEFT(B2,FIND("X",B2)-1) HELLOXGOODBYE
3 =LEFT(B3,FIND("X",B3)-1) HELLOXEVERYONE

This is a simplified example. the COUNTIF result should be 3 but is zero. If
this is because the range is a result of a formula then how do I use COUNTIF
on the formula result in A1:A10?

Thanks
Joe M.

 

[Don Guillett]

=SUMPRODUCT(--(LEFT(H2:H22,5)="hello"))

 

[David Biddulph]

The formula does return 3 for me. Perhaps you've got spaces before your
text? Does =LEN(A1) show 5?

 

[Joe M.]

David,

The length was the problem. The LEFT formula was returning a space at the
end of the result so I changed the LEFT to =LEFT(B1,FIND("X",B1)-2) and now
it works.

Thanks!
Joe M.

 

[David Biddulph]

You must, therefore, have had a space between the HELLO and the X in column
B.