I hit enter button too quick, here is a correct version

=SUMPRODUCT((UnitDate=--"1-Jan-2007")*(UnitDate<=--"31-Mar-2007")*(Bed=600)*(Bed<=649)*(answer="yes"))


"Teethless mama" wrote:

=SUMPRODUCT((UnitDate=--"1-Jan-2007")*(UnitDate<=--"31-Mar-2007")*(Bed=600)*(Bed<=649),(answer="yes"))


"ferde" wrote:

I work in a hospital. I have named beds 600-649 = 6South and 650-699= 6North.


6south= cells d1:d50 6north=e1:e50

I want to to count the YES answers in column c but I only want to count the
beds on 6south that had yes answers during Jan1 thru Mar 31
This formula is working great to give me the yes answers between these dates
but now I want to add one more criteria to the formula . Can anyone help me?

=SUMPRODUCT((UnitDate=--"1-Jan-2007")*(UnitDate<=--"31-Mar-2007"))




A B C
BED UnitDate answer
1 655 4/1/2007 yes
2 689 5/13/2007 no
3 601 1/5/2007 no
4 603 3/31/2007 yes
5 649 2/25/2007 yes



The answer to this example would be 6South Jan1-Mar31 = 2