Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF and leading zeros?
Excel 2003
I'm trying to run a very simple COUNTIF function, and it's not working. I have a list in column A of many product numbers. They are all set as text. Some have alpha characters others are pure numbers with some having leading zeros and others do not. (i.e. T002390, 2390, 002390) The list includes both 2390 and 002390 but only one occurance each. With... F2 = 2390 or F2 = 002390 I create the function... =COUNTIF(A:A,F2) With either criteria, the result is 2 instead of the expected 1. How can I get the COUNTIF to discern between 2390 and 002390? Is there a better formula for this purpose? Thanks, Chuck |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF and leading zeros?
I'm assuming F2 is also formatted as TEXT.
Try this: =COUNTIF(A:A,F2&"*") COUNTIF evaluates text numbers and numeric numbers as being equal. Using the wildcard forces COUNTIF to evaluate the criteria as text. -- Biff Microsoft Excel MVP "c mateland" wrote in message oups.com... Excel 2003 I'm trying to run a very simple COUNTIF function, and it's not working. I have a list in column A of many product numbers. They are all set as text. Some have alpha characters others are pure numbers with some having leading zeros and others do not. (i.e. T002390, 2390, 002390) The list includes both 2390 and 002390 but only one occurance each. With... F2 = 2390 or F2 = 002390 I create the function... =COUNTIF(A:A,F2) With either criteria, the result is 2 instead of the expected 1. How can I get the COUNTIF to discern between 2390 and 002390? Is there a better formula for this purpose? Thanks, Chuck |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF and leading zeros?
But product numbers beginning "2390..." such as "239012" shouldn't be
included. Maybe try one of: =COUNTIF(A:A,F2&"*")-COUNTIF(A:A,F2&"?*") =SUM(COUNTIF(A:A,F2&{"*","?*"})*{1,-1}) "T. Valko" wrote: I'm assuming F2 is also formatted as TEXT. Try this: =COUNTIF(A:A,F2&"*") COUNTIF evaluates text numbers and numeric numbers as being equal. Using the wildcard forces COUNTIF to evaluate the criteria as text. -- Biff Microsoft Excel MVP "c mateland" wrote in message oups.com... Excel 2003 I'm trying to run a very simple COUNTIF function, and it's not working. I have a list in column A of many product numbers. They are all set as text. Some have alpha characters others are pure numbers with some having leading zeros and others do not. (i.e. T002390, 2390, 002390) The list includes both 2390 and 002390 but only one occurance each. With... F2 = 2390 or F2 = 002390 I create the function... =COUNTIF(A:A,F2) With either criteria, the result is 2 instead of the expected 1. How can I get the COUNTIF to discern between 2390 and 002390? Is there a better formula for this purpose? Thanks, Chuck |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF and leading zeros?
If that's a possibility then I'd use:
=SUMPRODUCT(--(A1:A100=F2)) Note that SUMPRODUCT won't work using entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Lori" wrote in message ... But product numbers beginning "2390..." such as "239012" shouldn't be included. Maybe try one of: =COUNTIF(A:A,F2&"*")-COUNTIF(A:A,F2&"?*") =SUM(COUNTIF(A:A,F2&{"*","?*"})*{1,-1}) "T. Valko" wrote: I'm assuming F2 is also formatted as TEXT. Try this: =COUNTIF(A:A,F2&"*") COUNTIF evaluates text numbers and numeric numbers as being equal. Using the wildcard forces COUNTIF to evaluate the criteria as text. -- Biff Microsoft Excel MVP "c mateland" wrote in message oups.com... Excel 2003 I'm trying to run a very simple COUNTIF function, and it's not working. I have a list in column A of many product numbers. They are all set as text. Some have alpha characters others are pure numbers with some having leading zeros and others do not. (i.e. T002390, 2390, 002390) The list includes both 2390 and 002390 but only one occurance each. With... F2 = 2390 or F2 = 002390 I create the function... =COUNTIF(A:A,F2) With either criteria, the result is 2 instead of the expected 1. How can I get the COUNTIF to discern between 2390 and 002390? Is there a better formula for this purpose? Thanks, Chuck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading zeros | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) |