COUNTIF using a Wildcard

daveray

New Member
Joined
Mar 3, 2002
Messages
28
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

thelton

New Member
Joined
Sep 5, 2002
Messages
29
Have you tried using MSQuery that would be a easy way of searching and displaying the info you want.

Good luck
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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!
 

Forum statistics

Threads
1,144,052
Messages
5,722,249
Members
422,418
Latest member
Chipsy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top