Something Stupid

The range below is called database
Customer $$ Code
AA 25 NB
BB 10 NB
CC 10 NB

Value and Code is called Criteria

Value Code
25 NB =DCOUNTA(database, "Customer", Criteria)

Why did I get 3? I thought the answer is supposed to be 1? Any mistakes in my formula?? Help guys

[Aladin Akyurek]

Well, "Value" does not exist in "Database". You have to use the same label in the criteria range as well as in the database range. Arrange your data like this:

{"Value","Code","";
25,"NB","";
"","","";
"Customer","Value","Code";
"AA",25,"NB";
"BB",25,"NB";
"CC",10,"NB"}

e.g., in A1:C7. Name A1:B2 as "Criteria", A4:C7 as Database, and use:

=DCOUNTA(Database, 1, Criteria)

1 designates the Customer field, though any of [1,2,3] will do.

On 2002-03-10 22:58, Anonymous wrote:
The range below is called database
Customer $$ Code
AA 25 NB
BB 10 NB
CC 10 NB

Value and Code is called Criteria

Value Code
25 NB =DCOUNTA(database, "Customer", Criteria)

Why did I get 3? I thought the answer is supposed to be 1? Any mistakes in my formula?? Help guys

Hi, thanks, I fixed that... but the problem still persists... I want the criteria to be an AND, it seems excel interprets it as an OR. If you're interested, I'll mail you the xls file.

[Aladin Akyurek]

On 2002-03-10 23:36, Anonymous wrote:
Hi, thanks, I fixed that... but the problem still persists... I want the criteria to be an AND, it seems excel interprets it as an OR. If you're interested, I'll mail you the xls file.

I get 1 as result, not 3. If you like, use:

aky@sem.hhs.nl

[Dave Hawley]

Hi

I just set up a table like your uisng the COUNTA and it works fine, that is I got an answer of 1

I will email it to you if you like:

davidh@ozgrid.com

Hi Dave,
Could you please.... its maslan@fiberia.com
I feel really stupid today. Monday blues...

Thanks.

[Dave Hawley]

Pleasure, it's on its way.

You may also be interested in some slightly more advanced examples I have here:

http://www.ozgrid.com/download/default.htm

It's called:
DFunctionsWithValidation.zip

[Aladin Akyurek]

On 2002-03-10 23:36, Anonymous wrote:
Hi, thanks, I fixed that... but the problem still persists... I want the criteria to be an AND, it seems excel interprets it as an OR. If you're interested, I'll mail you the xls file.

Criteria range, as I said, must have labels that exist in the first row of the database.

You had in your WB "Value" instead of "Claimable".

The WB is underway to you.

Aladin