Combining DCOUNT with OR

D

dmaslen

I have the following data set, and I want to use database functions t
count the number of rows where any of the responses contain a valu
less than or equal to 4. The result should be 5. I have tried using th
DCOUNT function, but can't figure out how to get it to understand a
"OR" statement, i.e. Response 1 <=4 OR Response 2 <=4 OR Response 3 <=
OR Reponse 4 <=4

Type Response 1 Response 2 Response 3 Response 4
0 1 2 5 6
1 5 4 1 7
2 1 5 6 8
2 8 6 3 5
1 9 5 4 3
0 9 8 7 6

I need a function that is something like:

=DCOUNT(MasterDatabase,A1,G6:G10) where MasterDatabase is my datase
A1:E7, and G6:G10 is the "OR" criteria that will count any respons
that has a value of 4 or less.

Any suggestions?

Dav
 
F

Frank Kabel

Hi
with using a helper column try:
F2:
=--(COUNTIF(B2:E2,"<=4")>0)
copy down and sum column F
 
S

Stephen Bullen

Hi Dave,
=DCOUNT(MasterDatabase,A1,G6:G10) where MasterDatabase is my dataset
A1:E7, and G6:G10 is the "OR" criteria that will count any response
that has a value of 4 or less.
Click to expand...

In the criteria range for the Dxxx functions, values put into the same
row are 'AND'ed, while values put into separate rows are 'OR'd, so you
could set up the criteria range (for OR'ing 3 responses) as:

G H I
1 Response 1 Response 2 Response 3
2 <=4
3 <=4
4 <=4

Then use $G$1:$I$4 as the criteria range

Alternatively, you could use a calculated criteria range formula:

G
1 Calc1
2 =OR(B2<=4,C2<=4,D2<=4)

and use $G$1:$G$2 for the criteria range, where the field 'Calc1'
*doesn't* appear in your database, the first data row is row 2 and the
three responses to OR are in columns B, C and D.

This and more is covered in some detail in our book "Professional Excel
Development", due out in February.
The MasterDatabase is also a dataset that is being pulled from an
Oracle database using Microsoft Query, so when I refresh the data, I
want this function to update itself automatically.
Click to expand...

So you could also include an extra field in the query itself, to do the
'OR'ing, which would probably be faster than lots of Dxxx functions.
I can't be copying
and pasting functions every time I do a refresh.
Click to expand...

Actually, you can. In the Query Table properties page, there's a tick
box for 'Copy down formulas in adjacent columns', which does it for you
<g>.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk
 
Top