DCounta doesn't work

[Rambo]

Hi, I have the following condition:

Zone Score
LoMt-1 100
LoMt-1
LoMt-1 0
LoMt-1 50
LoMt-1 100
LoMt-2 0
LoMt-2
LoMt-2 50
LoMt-2 100
LoMt-2

I try to use "Dcounta" function to count a specific zone without any score.
It gives me "VALUE!". Why? The correct answer should be only 1 blank in
LoMt-1 and 2 blank in LoMt-2 based on the above condition.

Can you please help with the function either "dcounta" or "sumproduct"?
Thanks in advance.

 

[Per Jessen]

Hi

WIth your data in column A:B you can use this formula:

=SUMPRODUCT(--(A2:A11="LoMt-1"),--(B2:B11=""))

Regards,
Per

 

[Rambo]

Thanks for your reply, Per.

What happen if I have 3,000 data in Column A and B? Can I search for the
whole column for LoMt-1?

It doesn't work for me the formula below. Please advise.
=SUMPRODUCT(--(A:A="LoMt-1"),--(B:B=""))
The result is "#NUM!".

Rambo

 

[Max]

=SUMPRODUCT(--(A:A="LoMt-1"),--(B:B=""))

The result is "#NUM!"

Think you can't use entire col refs for sumproduct in xl2003. Just define it
to cover the smallest range which is large enough for the max expected
extents, eg:
=SUMPRODUCT(--(A2:A3500="LoMt-1"),--(B2:B3500=""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Per Jessen]

Thanks for your reply.

As Max says, you can not use an entire column as reference before
excel 2007.

Solve it as Max suggest.

Regards,
Per

 

[Ashish Mathur]

Hi,

If you wish to use the DCOUNTA function, you may try the followng:

1. Assume that the data is in range B3:C13 (including the headers);
2. In cell B15 and D15, enter Zone. Leave cell C15 blank;
3. In B16:B17, enter LoMt-1 and LoMt-2 respectively
4. In cell C16 and C17, enter =isblank(c4);
5. In cell D17, enter =DCOUNTA($B$3:$C$13,D$15,$B$15:C16)-SUM(D$1515) and
copy down.

The answer would be 1 and 2.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com