MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dfunctions - Criteria Range


Posted by Chris on July 23, 2001 12:27 PM

Hey All,
I've run up against wall with the DCOUNT function and my criteria range. My criteria range has forumlas in the criteria cells (i.e. if(x=y,TRUE,"") ). My problem is that the result "" causes the DCOUNT to NOT work. Is there another result I could put in this formula so that it will work the way a BLANK criteria cell is supposed to work? (i.e. like a wildcard)


Posted by cpod on July 23, 2001 2:00 PM

How about:

if(x=y,TRUE,"<>TRUE")

Posted by Mark W. on July 23, 2001 2:04 PM

It appears that you are attempting to create a
computed criteria. Computed criteria always return
a boolean result, and <blank> or "" aren't boolean.
There's your 1st problem. =x=y would be a more
appropriate formula for a computed criteria. What
cell reference are you substituting for x, y?
What do you have entered in the first row of
criteria range column that contains this formula?

Posted by Chris on July 23, 2001 2:54 PM

I think I've got a workaround... the "&LT;&GT;TRUE" wouldn't work, since I need to also include TRUE, i.e. "&LT;&GT;=True". Here's a little more detail on the scenario...
I have a database of patient data (hematology):
wbc plt mvc hgb ...etc
.7 .3 3 TRUE

some of this data is a numerical result, some of it is True/False flag results. Depending on which procedure/test is performed on the patient only some of the results comeback and are entered in the database, the remaining unused results are entered as 0. I have to test for certain criteria depending on which procedure was performed... the problem I was having was this: If a certain procedure was used, it wouldn't test the true/false flag criteria. I thought I could have an IF(Procedure = X,TRUE,"") in the DCOUNT criteria field... no go. My work around was to set up a second row of criteria. If the procedure which excluded the true/false flag was run, the first row tests for TRUE, and the second row tests for FALSE (all inclusive basically). If a different procedure is run that tests against that TRUE/FALSE flag, then both rows equal TRUE or FALSE, depending on which one the user wants to test for......
my apologies for the long windedness (and also in case none of that made sense...)
I think I've got it working now, thanks for the comments.

Posted by Mark W. on July 23, 2001 3:16 PM