Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
I have a raw data worksheet that I populate using the OLEDB Jet
provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
You can use sumproduct instead
=SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message ups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
I don't think SUMPRODUCT will accomplish what I'm looking for.
Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message ups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
Yes it will work
for instance =SUMPRODUCT(--(A2:A100=8),--(B2:B100<"11"),--(C2:C100="M")) will count where B2:B100 is NOT text 11 and where A2:A100 equals 8 and C2:C100 equals "M" -- Regards, Peo Sjoblom "MJP" wrote in message ps.com... I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message ups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
My fault, you are correct!
Thanks so much. I'll look deeper into what exactly the SUMPRODUCT function is doing. Do you know why this doesn't work using the DCOUNT function? Seems like a bug to me that's fairly annoying and cost me a considerable amount of time troubleshooting. Thanks again. On Jun 4, 11:40 am, "Peo Sjoblom" wrote: Yes it will work for instance =SUMPRODUCT(--(A2:A100=8),--(B2:B100<"11"),--(C2:C100="M")) will count where B2:B100 is NOT text 11 and where A2:A100 equals 8 and C2:C100 equals "M" -- Regards, Peo Sjoblom "MJP" wrote in message ps.com... I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message roups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
Interesting problem. To force a text comparison, you could try using
two criteria: Code <11* 11?* Repeat any other criteria on the second line and also make sure to use DCOUNTA for text values. Alternatively use a calculated criteria e.g. =A2<"11" although it will run slower. On 4 Jun, 16:26, MJP wrote: I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message oups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
Lori,
Changing my criteria to <11* seemed to work as well at first glance. What exactly is that doing? If I have data values such as 111, or 11A, etc in my code column is it going to filter them out as well? Thanks. On Jun 4, 12:01 pm, Lori wrote: Interesting problem. To force a text comparison, you could try using two criteria: Code <11* 11?* Repeat any other criteria on the second line and also make sure to use DCOUNTA for text values. Alternatively use a calculated criteria e.g. =A2<"11" although it will run slower. On 4 Jun, 16:26, MJP wrote: I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message oups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
It will filter out 111 as well, you can clear the header in the criteria and
use the formula Lori provided =A2<"11" where A2 is the first cell with data in the code range so if you previously had Code <11* in let's say F1:F2, leave F1 blank and put =A2<"11" in F2 There are other drawback using the D functions, for instance they will not work if the workbook is closed whereas SUMPRODUCT will work "MJP" wrote in message ups.com... Lori, Changing my criteria to <11* seemed to work as well at first glance. What exactly is that doing? If I have data values such as 111, or 11A, etc in my code column is it going to filter them out as well? Thanks. On Jun 4, 12:01 pm, Lori wrote: Interesting problem. To force a text comparison, you could try using two criteria: Code <11* 11?* Repeat any other criteria on the second line and also make sure to use DCOUNTA for text values. Alternatively use a calculated criteria e.g. =A2<"11" although it will run slower. On 4 Jun, 16:26, MJP wrote: I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message oups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
Thanks again Peo that seems to work as well, though it sounds like
SUMPRODUCT is my best option. On Jun 4, 12:24 pm, "Peo Sjoblom" wrote: It will filter out 111 as well, you can clear the header in the criteria and use the formula Lori provided =A2<"11" where A2 is the first cell with data in the code range so if you previously had Code <11* in let's say F1:F2, leave F1 blank and put =A2<"11" in F2 There are other drawback using the D functions, for instance they will not work if the workbook is closed whereas SUMPRODUCT will work "MJP" wrote in message ups.com... Lori, Changing my criteria to <11* seemed to work as well at first glance. What exactly is that doing? If I have data values such as 111, or 11A, etc in my code column is it going to filter them out as well? Thanks. On Jun 4, 12:01 pm, Lori wrote: Interesting problem. To force a text comparison, you could try using two criteria: Code <11* 11?* Repeat any other criteria on the second line and also make sure to use DCOUNTA for text values. Alternatively use a calculated criteria e.g. =A2<"11" although it will run slower. On 4 Jun, 16:26, MJP wrote: I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message oups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT problem with numbers stored as text
The reason you might want to get away from the D functions is that they are
a bit archaic, they are not as easy to adapt as SUMPRODUCT. The only reason I learned them was because the criteria is similar to the advanced filter which can be very useful in some situations. Sometimes it's hard to see the logic when you set up a criteria Also, it's harder to find support for them since so few use them -- Regards, Peo Sjoblom "MJP" wrote in message ups.com... Thanks again Peo that seems to work as well, though it sounds like SUMPRODUCT is my best option. On Jun 4, 12:24 pm, "Peo Sjoblom" wrote: It will filter out 111 as well, you can clear the header in the criteria and use the formula Lori provided =A2<"11" where A2 is the first cell with data in the code range so if you previously had Code <11* in let's say F1:F2, leave F1 blank and put =A2<"11" in F2 There are other drawback using the D functions, for instance they will not work if the workbook is closed whereas SUMPRODUCT will work "MJP" wrote in message ups.com... Lori, Changing my criteria to <11* seemed to work as well at first glance. What exactly is that doing? If I have data values such as 111, or 11A, etc in my code column is it going to filter them out as well? Thanks. On Jun 4, 12:01 pm, Lori wrote: Interesting problem. To force a text comparison, you could try using two criteria: Code <11* 11?* Repeat any other criteria on the second line and also make sure to use DCOUNTA for text values. Alternatively use a calculated criteria e.g. =A2<"11" although it will run slower. On 4 Jun, 16:26, MJP wrote: I don't think SUMPRODUCT will accomplish what I'm looking for. Maybe I oversimplified my problem. There are many other columns in my raw data worksheet. My DCOUNT functions use many of these columns in order to get a count of the rows that fall under its criteria. For example the criteria might be: Month =8 Code <11 Code <K5 Sex =M All parts of the criteria seem to work except it is not filtering out the rows where the Code column equals 11 stored as text. It works if I format such cells as numeric, but again this is not an option when I am programatically populating the raw data. Thanks. On Jun 4, 11:10 am, "Peo Sjoblom" wrote: You can use sumproduct instead =SUMPRODUCT(--(Range<"11")) -- Regards, Peo Sjoblom "MJP" wrote in message oups.com... I have a raw data worksheet that I populate using the OLEDB Jet provider. Other worksheets reference the raw data and contain functions to perform various counts of the data. One column in the raw data contains both textual values (i.e. "K5") and numeric values (ie. 11). Because I am using the Jet provider, the only way I have found to avoid data format errors on import is to pre- format the column as General or Text. So, the values such as 11 are actually numbers stored as text (I look in the function bar and see the value prepended with an apostrophe: '11). One of the other worksheets references this column in a DCOUNT function and is looking for a count where this column is not equal to 11. (<11). However it's not filtering out the values of 11 because they are numbers formatted as text. I have seemingly tried everything to try and do a string comparison but no luck. <"11", <'11, etc nothing works. How can I get this function to work properly on numbers stored as text without having to change the format of the raw cells to text (doesn't seem to be an option with columns of mixed types using the Jet provider)? Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) |