Using ADD function within DCOUNT criteria

[Guest]

I am trying to use a DCOUNT function, and need to have two criteria. How do
I use the AND function within the criteria?

I can do it using one criteria at a time, but when I use AND to use two
criteria, I get an error.

What sytax do I use?

Many thanks

 

[Earl Kiosterud]

Dave,

I don't think you can, unless you set up a helper column that has your
conditions, then use that in your DCOUNT function. Something like:
=AND(A2>0, A2<100, B2 = "Normal").
This one's for A2 values between 0 and 100, and B2 contains the word Normal.

Or use SUMPRODUCT instead:
=SUMPRODUCT( (A2:A100 > 0) * (A2:A100 < 100) * (B2:B100 = "Normal) )

--
Earl Kiosterud
www.smokeylake.com/

Off-topic: There's a hilarious comedy act by Men in Coats at
http://www.koreus.com/files/200505/men-in-coats.html. You gotta see this.
I had to share with all.

 

[Debra Dalgleish]

You can use two columns in the criteria area, similar to the advanced
filter criteria area shown here:

http://www.contextures.com/xladvfilter01.html#Criteria

 

[Earl Kiosterud]

Dave,

Oops. Cancel the "I don't think you can." I read your post as using
COUNTIF, not DCOUNT. I think I'll go back to painting the guest room now.

 

[Guest]

I have managed it. In the criteria range, you have to have the two columns
side by side and select both as one range, (without trying to use AND). If
you try using the AND function and the columns are separated, it just does
not work.

Thanks for your help anyway.

DaveF