sumproduct and IF criteria??

[Guest]

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

 

[Guest]

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

 

[Guest]

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"))

 

[Dave Peterson]

I don't see how this counts just the Yes's.

But maybe...

For the North:
=SUMPRODUCT((UnitDate>=--"1-Jan-2007")*(UnitDate<=--"31-Mar-2007")
*(answer="yes")*(bed<650))


For the South:
=SUMPRODUCT((UnitDate>=--"1-Jan-2007")*(UnitDate<=--"31-Mar-2007")
*(answer="yes")*(bed>649))

 

[Guest]

Thank you IT WORKS GREAT. We have a 1000 bed hospital with 20 different
floors. I'm wondering if I can use a NAMED list of beds that the formula
could pull from.

Is there a way to arrange a table that the formula would refer to and then
be able to tell which floor the bed belongs in?

I hope I'm making myself clear. Even if I cant do it anothe way...the
formula you gave me works great and I will plug them in as many times as nec.
Thank you

A B C D E F
G H
1north 1south 2north 2south 3north 3south 4north 4 south
100 151 200 251 300 351 400
451
101 152 201 252 301 352 401
452
102 153 203 253 303 353 403
453

 

[Guest]

I think we were typing a response at the same time but thank you for the
reply it works well ... I am wondering if you can help me if I pull from a
named range for the bed numbers see last string ...