COUNTIF using a Wildcard

[daveray]

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

 

[thelton]

Have you tried using MSQuery that would be a easy way of searching and displaying the info you want.

Good luck

 

[Aladin Akyurek]

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!