Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
sumif when criteria is a range | Excel Discussion (Misc queries) | |||
Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
Specify a null value in an Excel Database criteria range | Excel Worksheet Functions | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions |