multiple logical tests as argument in Database function

Wouldn't you just add the criteria twice, once with a <26 test and the other with >66 test, ie

=DCOUNT(A1:A10,1,C1:D2)

Where C1:D1 = A1 presumably and C2:D2 hold < 26, >66 respectively

Post a file so we can see what you're working with in terms of ranges etc...

Originally Posted by DonkeyOte

Wouldn't you just add the criteria twice, once with a <26 test and the other with >66 test, ie

=DCOUNT(A1:A10,1,C1:D2)

Where C1:D1 = A1 presumably and C2:D2 hold < 26, >66 respectively

Post a file so we can see what you're working with in terms of ranges etc...

Sorry, don't know how to post a file or snippet. I tried the manage attachments link and get a message that I'm not signed in or don't have permission, but I am signed in so maybe I don't post frequently enough or it may be some weird cookie thing. This happens to me all the time with the particular forum engine used here and elsewhere. I''ve signed in, gone through the welcome screen and been redirected to the page I was working on. Up in the right hand corner it says welcome, riwiseuse. But I go to post and get a reply that says I can't do that because I'm not signed in. So I sign in again and sometimes it just goes ahead and makes my post and sometimes it's just lost somewhere and I have to start over, so I've gotten pretty careful to copy my posts to the clipboard before clicking "post" . Sorry for that. You're the functions forum, not the IT manager, and this is not peculiar to excelforum. Every forum I use that the same thread engine you do gives me the same trouble.

Anyway,

my formula looks like this:

=DCOUNT(A1:AA4191,2,A4267:AA4268)

In the target criteria range I place 1/2 of a conditional statement, e.g., <100, in the criteria column that corresponds to the column (or field) I want to use for case selection. As you can see with the coordinate column designations from the database and criteria, I line up my criteria under the database so the field letters correspond, but I don't think that is necessary so long as the criteria has the same number of fields as the database.

Assuming that the various case selection criteria are met in a particular record, then this function adds 1 to the count for each record that has a non-text ,non-blank value in field 2.

As you can see from the range there are 27 fields in the database, and I can place a single conditional test in any or all of these fields so I am able to run multiple tests at once. However, my problem is multiple conditionals against a single field.

So I have no problem if I have a column for age and one for weight, counting people who are over 30 and under 150 lbs. because those tests are entered in different criteria fields. But If I want to find those over 30 but not 55, where both tests have to be applied to the age field, I cannot figure out how to do it.

I tried making up a quick test database to use your example and it does not work. I assume this is because it is a single field database but you have specified a two field criterion, i.e. the database has one field in column A, but the criterion specified has two fields in columns C and D. It seems that you weere thinking when you specified field 1 as the second argument in your sample function that the criteria would be used to test field 1, but field 1 is only tested for a non-text, non-blank value, the ordinary function of =COUNT. The limitations or "case selection" are effected by the relation of the conditionals in the criteria range to the relevant fields in the database, not to the field specified for the purposes of counting.

Hope this explanation is helpful.

Thanks,

Brian

You need to have <26 on one line and >66 on another in your criteria range to get an OR criterion.

I tried making up a quick test database to use your example and it does not work. I assume this is because it is a single field database but you have specified a two field criterion, i.e. the database has one field in column A, but the criterion specified has two fields in columns C and D. It seems that you weere thinking when you specified field 1 as the second argument in your sample function that the criteria would be used to test field 1, but field 1 is only tested for a non-text, non-blank value, the ordinary function of =COUNT. The limitations or "case selection" are effected by the relation of the conditionals in the criteria range to the relevant fields in the database, not to the field specified for the purposes of counting.

See attached - the key is the point that header in C1:D1 = A1 ... ie 2 criterion for the same field.

Originally Posted by DonkeyOte

See attached - the key is the point that header in C1:D1 = A1 ... ie 2 criterion for the same field.

Got it. I actually did that correctly in my test, but when I specified the database, I wasn't paying close attention and my range included only the values and left the header out, so it didn't match to either criteria, and i assumed it was confused because the number of fields in the criteria was greater than the database.

I looked in the other opitons for posts to see how you mark a thread as "SOLVED", but I couldn't find it. Maybe that is just the discretion of the moderator, or is that something I can do?

Thanks,

Brian

PS - I still think, a single cell function of some sort that takes multiple arguments similar to an AND function would allow you to leave a large multi field database more logically related to its criteria but using two fields both titled the same as the field you are applying them to works fine.

Originally Posted by riwiseuse

I looked in the other opitons for posts to see how you mark a thread as "SOLVED", but I couldn't find it. Maybe that is just the discretion of the moderator, or is that something I can do?

See the first box under the Search options in the FAQ section:

http://www.excelforum.com/faq.php