COUNTIF on result of formula
 

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.

COUNTIF on result of formula
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
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.

COUNTIF on result of formula
 

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

"Joe M." wrote in message
...
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.

COUNTIF on result of formula
 

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" wrote:

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

"Joe M." wrote in message
...
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.

COUNTIF on result of formula
 

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