# Countif with wildcards

1. ## Countif with wildcards

hi there.

i have a column of telephone numbers taken from a program we use which gives us a raw data.

all i am trying to do for the last 2 hours is to do a count of all cells where the number is starting with "7".

I used the formula: =COUNTIF(A1:B8,"7*")

It just keeps coming with zero as the result. Not sure whether its to do with the formatting of the cells.  Register To Reply

2. ## Re: Countif with wildcards

If you click on one of those cells that should be counted, what do you see in the formula bar?  Register To Reply

3. ## Re: Countif with wildcards

This may not be the most sophisticated answer, but you could add a column somewhere (let's say column E) and in the first cell (E1) use the formula =LEFT(A1,1). Then copy that down. Then your count formula can simply be =COUNTIF(E1:E8,"7") or =COUNTIF(E:E,"7)

Once you set that up and copy the LEFT() formula down to more rows than you will ever use, you can just hide the column with the first digits so you don't have to look at it.  Register To Reply

4. ## Re: Countif with wildcards

Wildcards don't work with numeric values, try using SUMPRODUCT and LEFT, i.e.

=SUMPRODUCT((LEFT(A1:B8)="7")+0)  Register To Reply

5. ## Re: Countif with wildcards

Or just:

=SUMPRODUCT(--(LEFT(H2:H4)="7"))

copied down.. no helpers...  Register To Reply