DCounta doesn't work

  • Thread starter Thread starter Rambo
  • Start date Start date
R

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.
 
Hi

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

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

Regards,
Per
 
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
 
=SUMPRODUCT(--(A:A="LoMt-1"),--(B:B=""))
The result is "#NUM!"
Click to expand...

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
---
 
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
 
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$15:D15) and
copy down.

The answer would be 1 and 2.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top