Countif on zip codes that vary in format

D

Don

A Zip Code list may contain: 12345 and 12345-6789 and 123456789

The reference cell "D1" on a seperate sheet = 12345

I want to know the number of occurances of the value in "D1" in the Zip Code
List and I need to fill down 1800 rows (D1 to D1800) with the formula.

=COUNTIF('10 yrs'!I:I,D1) returns a count of 12345 within the list but does
not capture the occurance of 12345 within the other longer strings. I cannot
seem to add a wildcard to the "D1" value.

Any thought on how I can get there?
 
T

T. Valko

Wildcards don't work on numbers. It would work on 12345-6789 which is a text
string but it won't work on the other iterations which are numbers.

Do you have any codes with leading zeros? Like:

01234
012345678

If so, do you know if these entries are formatted as TEXT or do they use a
custom number format to display the leading zero?

As a start you can try this:

=SUMPRODUCT(--(LEFT('10 yrs'!I1:I1000,5)=D1&""))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.
 
D

Don

WOW! Quick reply...

=SUMPRODUCT(--(LEFT('10 yrs'!I:I,5)=D24&"")) WORKS and I can fill down.

Thanks.
 
T

T. Valko

I don't think that'll work. In the U.S. we have 2 kinds of postal zip codes,
a 5 digit number and what they call zip+4 which is a 5 digit number followed
by a hyphen and 4 more digits.

12345
12345-1234

In addition to those the OP also said they had what is the equivalent of the
zip+4 without the hyphen or a 9 digit number.

12345
12345-1234
123451234


So, if they were looking for the 5 digit zip12345, that formula would give a
false positive because it would find 12345 in a code like 123412345
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to format varying zip codes... 9
Format Zip Code for Barcoding 4
Web Query 0
Formatting Zip Codes 3
Zip Code Query 2
Insert Zip codes into weather code 5
do i use countif? 2
Filter ZIP codes into ranges 6

Top