| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on zip codes that vary in format
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
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. -- Biff Microsoft Excel MVP "Don" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
WOW! Quick reply...
=SUMPRODUCT(--(LEFT('10 yrs'!I:I,5)=D24&"")) WORKS and I can fill down. Thanks. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Don" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
Hi,
You can also use the following array formula (Ctrl+Shift+Enter) COUNT(SEARCH(D1,A1:A2,1)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Don" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Don" wrote in message ... WOW! Quick reply... =SUMPRODUCT(--(LEFT('10 yrs'!I:I,5)=D24&"")) WORKS and I can fill down. Thanks. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Don" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
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 -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, You can also use the following array formula (Ctrl+Shift+Enter) COUNT(SEARCH(D1,A1:A2,1)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Don" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif on zip codes that vary in format
Hi,
That formula incorrectly counts this 212345789 -- Thanks, Shane Devenshire "Ashish Mathur" wrote: Hi, You can also use the following array formula (Ctrl+Shift+Enter) COUNT(SEARCH(D1,A1:A2,1)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Don" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to format varying zip codes... | Excel Worksheet Functions | |||
| Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
| Vary the criteria in countif | Excel Discussion (Misc queries) | |||
| how to format zip codes | Excel Discussion (Misc queries) | |||
| I am not able to format cells for zip codes | Excel Worksheet Functions | |||
Linear Mode
