Look for a given number in a range

[ManUBlueJay]

I am looking for a way to count how many times a given number apears in my range. The number I am looking for is based in Cell A1
Example. Cell A1 = 25

Range I am looking in is Col B1 to B100.
The cells will either have whole numbers or a whole number with a single letter at the end. The formula in this case should equal 4

12
12b
20
20c
25
25a
25b
12d
25e

Any help would be appreciated

 

[Rick Rothstein]

This array-entered** formula seems to work...

=SUM(IFERROR(0+(0+LEFT(B1:B100,2)=A1),0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Fluff]

How about

=COUNTIF(B1:B100,A1&"?")+COUNTIF(B1:B100,A1)

 

[ManUBlueJay]

Thank you. Both of them work, I will go with the second to avoid the array

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ManUBlueJay]

Similar Question but how do I find how many rows on the next whole number would be.
So if I change A1 to "12" the next non text number would be 2 rows on from 12.
SO it needs to find A1 and look for the next number after.

 

[Fluff]

As this is a totally different question, you will need to start a new thread. Thanks