# COUNTIF using a Wildcard

#### daveray

##### New Member
I am using a multiple COUNTIF statement to locate all clients in a set postcode. This is used for territory management in realtion to salesman/client ratios.

=COUNTIF('sales area'!C:C, "BS1*")+COUNTIF('sales area'!C:C, "BS2*")+ etc

I have the formula in the cell adjacent the saleman's name and the search checks multiple sheets within the same workbook.

The search is incorrect because unfortunately I have postcodes that are BS11, BS12, BS13 etc.

What can I use that will differentiate between postcodes in BS1 and BS1X or between BS2 and BS2X (where X is the next part of the postcode eg BS15, BS16 or BS20, BS21)

Any help would be welcomed

Thanks

Dave

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Have you tried using MSQuery that would be a easy way of searching and displaying the info you want.

Good luck

On 2002-09-19 11:49, daveray wrote:
I am using a multiple COUNTIF statement to locate all clients in a set postcode. This is used for territory management in realtion to salesman/client ratios.

=COUNTIF('sales area'!C:C, "BS1*")+COUNTIF('sales area'!C:C, "BS2*")+ etc

I have the formula in the cell adjacent the saleman's name and the search checks multiple sheets within the same workbook.

The search is incorrect because unfortunately I have postcodes that are BS11, BS12, BS13 etc.

What can I use that will differentiate between postcodes in BS1 and BS1X or between BS2 and BS2X (where X is the next part of the postcode eg BS15, BS16 or BS20, BS21)

Any help would be welcomed

Thanks

Dave

=COUNTIF('sales area'!C:C,"BS1")

would differentiate between BS1 and non-BS1!

Replies
2
Views
398
Replies
42
Views
3K
Replies
4
Views
1K

1,218,660
Messages
6,143,747
Members
450,502
Latest member
sakaria_123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back