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 -