DCounta doesn't work
 

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.

DCounta doesn't work
 

Hi

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

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

Regards,
Per


On 10 Aug., 02:13, Rambo wrote:
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.

DCounta doesn't work
 

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

"Per Jessen" wrote:

Hi

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

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

Regards,
Per


On 10 Aug., 02:13, Rambo wrote:
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.

DCounta doesn't work
 

=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
---

DCounta doesn't work
 

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

On 10 Aug., 03:01, Max wrote:
=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
Singaporehttp://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

DCounta doesn't work
 

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

"Rambo" wrote in message
...
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.