Calculating counts based on two sets of criteria

[Brendon]

I have the following function

=SUMPRODUCT(--(PTORDTIME<>""),--(PTORDTIME>="06:30"),--
(PTORDTIME<"06:29"),--(PTORDDATE=A7))

Basically, what I'm trying to do is get a patient count between 6:00 -
6:29 on the date that's in Cell A7 (2/17/09)

PTORDTIME and PTORDDATE is on a different sheet, but within the same
workbook.

 

[Dave Peterson]

=SUMPRODUCT(--(PTORDTIME<>""),
--(PTORDTIME>=time(6,0,0)),
--(PTORDTIME<time(6,30,0)"),
--(PTORDDATE=A7))

I changed the 6:30 to 6:00, too.

 

[Brendon]

So I made the updates, and it still gives me an error (#VALUE!). Does
it matter that the Named Range is on a different sheet?

 

[Dave Peterson]

Nope.

But it will matter that all the ranges are the same size.

And all of the ranges can't have errors in them (text is ok with this syntax).

 

[Brendon]

Excellent, I was missing the "ranges have to be the same size" trick.
Works like a charm.

Thanks Dave.