Counting a range of cells that include text and numbers

[jbourne21]

Hey guys,

So I have been scouring the site looking for help with a particular single-cell formula to count a range of cells that have both text and numbers. Easiest way to explain is with an example:

SalesID 10
SalesID 13
SalesID 17
SalesID 21
SalesID 28
SalesID 31
SalesID 32
SalesID 33
SalesID 37
SalesID 38
SalesID 43
SalesID 53

What would be the right formula to find the number of SalesID's in the 30-40 range without separating the numbers from the text into a separate column?

Thanks in advance for the help!

[Pete_UK]

You can use wildcards with COUNTIF, so something like this:

=COUNTIF(A:A,"SalesID 3*")

This will, of course, also include SalesID 3 if you have it like that, but not if you have SalesID 03. Similarly if you have any in the 300's, but you can overcome that and the SalesID 3 by using:

=COUNTIF(A:A,"SalesID 3?")

so it will only count if there is a single character after the 3.

Hope this helps.

Pete

[jbourne21]

You can use wildcards with COUNTIF, so something like this:

=COUNTIF(A:A,"SalesID 3*")

This will, of course, also include SalesID 3 if you have it like that, but not if you have SalesID 03. Similarly if you have any in the 300's, but you can overcome that and the SalesID 3 by using:

=COUNTIF(A:A,"SalesID 3?")

so it will only count if there is a single character after the 3.

Hope this helps.

Pete

Hi Pete

Thank you so much for getting back to me on this. This is much easier than what I was attempting with Right formulas within Countif's and Sumproducts! It was the "?" behind the 3 which I needed. Thanks so much!!

[Mad Moose]

I have a similar problem
I wish to count the number of cells which contain only text but all the cells in the range contain a formula and countif seems to be counting these cells as well

is there a way just to count text cells and ignore empy cells containing the formulas which have not been populated with text yet

[nahidking]

1. In B1 put =VALUE(RIGHT(A1,2)) and drug down.
2. In C1 Put =COUNT(B1:B12)-COUNTIF(B1:B12,"<30")-COUNTIF(B1:B12,">40")