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

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