# 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.

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?

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.

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)

5. ## Re: Countif with wildcards

Or just:

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

copied down.. no helpers...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1