How do I add more ranges to the following formula?

[neivamj]

(for Excel 2003)
I've a Yearly calendar and a table with around 27 diferent date ranges for 2
teams (alternating). I want the days in the calendar to change color
according to who is working ex. Red for Team1 and Green to Team2.

In each day of the calendar I used the folowing formula in Condition 1 and
the same with inverse False,True result in Condition 2
=IF(OR(AND(S6>$AA$5,S6<$AB$5),AND(S6>$AA$7,S6<$AB$7),AND(S6>$AA$9,S6<$AB$9),AND(S6>$AA$11,S6<$AB$11),AND(S6>$AA$13,S6<$AB$13),AND(S6>$AA$15,S6<$AB$15),AND(S6>$AA$17,S6<$AB$17),AND(S6>$AA$19,S6<$AB$19)),TRUE,FALSE)

This works for up to 8 ranges. How do I do the same with some other formula
that can accept more ranges?

 

[T. Valko]

Try this...

=SUMPRODUCT(--(MOD(ROW(AA5:AA19)-ROW(AA5),2)=0),--(S6>AA5:AA19),--(S6<AB5:AB19))

 

[neivamj]

Thanks, it workwed perfectly.

--
Mario

Try this...

=SUMPRODUCT(--(MOD(ROW(AA5:AA19)-ROW(AA5),2)=0),--(S6>AA5:AA19),--(S6<AB5:AB19))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!