Criteria Range at two different places
 

Hello.

I have a problem that I can not figure out how to handle.

I have this DCOUNT function: =DCOUNT(Database;DATA!$L$10;(B79:B80))

It works but now I want to add another criteria range at another place
in my workbook.

I try to do it like this:
=DCOUNT(Database;DATA!$L$10;(B79:B80;A107:A108))
but it does say #Value!. What can be wrong.
The two criteria ranges are B79:B80 and A107:A108.
How can I change the above function so that I allso take A107:A108 in
concideration?

I hope I have contributet enough information but if I have forgotten
something, please ask and I will give it to you.

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=534945

Criteria Range at two different places
 

With DCOUNT you can not split up your criteria in non-adjacent ranges. They
have to be next to each other. If you criteria must be plit up into
non-adjacent ranges I would suggest using the SUMPRODUCT function to
accomplish your task.

=SUMPRODUCT(--firstrange=firstcriteria),--(secondrange=secondcriteria))

The trues will turn into 1's and the fales to 0's. If both first and second
is true the formula will multiply 1 * 1 which equals 1. It will then add up
all the ones to give you a count.

Play around with the function, Excel help, and posts regarding SUMPRODUCT
and you will find what you need.

Hope this helps.

Thanks,
Bill Horton

"a94andwi" wrote:


Hello.

I have a problem that I can not figure out how to handle.

I have this DCOUNT function: =DCOUNT(Database;DATA!$L$10;(B79:B80))

It works but now I want to add another criteria range at another place
in my workbook.

I try to do it like this:
=DCOUNT(Database;DATA!$L$10;(B79:B80;A107:A108))
but it does say #Value!. What can be wrong.
The two criteria ranges are B79:B80 and A107:A108.
How can I change the above function so that I allso take A107:A108 in
concideration?

I hope I have contributet enough information but if I have forgotten
something, please ask and I will give it to you.

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=534945