Counting a value in a range ingnoring last digit

Originally Posted by starguy

Data is

2.2t
2.2d
2.4a
2.3d
2.2a

formula should count value 2.2 (ignoring t, d, a at the end) and return 3
note that I cannot use helper column to extract data except last digit. I have to count on same data.

Regards

Hi starguy,

If your data is in col A, then in B1

=LEFT(A1,3), copy this down, then in cell B6

=COUNTIF(B1:B5,"2.2")

Answer = 3

oldchippy

thanks for replying but I have already mentioned that I cannot use any helper column to extract data, cannot use =LEFT(A1,3) for some reason, I want to directly count 2.2.

Regards

Originally Posted by oldchippy

Hi starguy,

If your data is in col A, then in B1

=LEFT(A1,3), copy this down, then in cell B6

=COUNTIF(B1:B5,"2.2")

Answer = 3

oldchippy

Originally Posted by starguy

thanks for replying but I have already mentioned that I cannot use any helper column to extract data, cannot use =LEFT(A1,3) for some reason, I want to directly count 2.2.

Regards

OK, try this

=COUNTIF(A1:A5,"2.2"&"*")

oldchippy

thanks it worked

Originally Posted by oldchippy

OK, try this

=COUNTIF(A1:A5,"2.2"&"*")

oldchippy

Thanks for the feedback - glad to be of assistance