If Sumproduct Help

[Kevin3]

Hello,

I am attempting to build a formula that displays the status of employees on a calendar. I am trying to get the "Value If True" portion of the formula to change based on the status selected. So, if Vacation is selected, then "Vac" is displayed. If "Sick" is selected, then "Vac" changes to "Sick". Any help would be appreciated.

This is the Formuala I'm using now:
=IF($A8<>"",IF(SUMPRODUCT((Event!$M$6:$M$501<=E$4)*(Event!$N$6:$N$501>=E$4)*(Event!$K$6:$K$501=$A8)*(Event!$L$6:$L$501="Vacation")),"Vac",IF(SUMPRODUCT((Event!$M$6:$M$501<=E$4)*(Event!$N$6:$N$501>=E$4)*(Event!$K$6:$K$501=$A8)*(Event!$L$6:$L$501="Sick")),"Sick","")),0)

This is the Formula I'm trying to build:

=IF(SUMPRODUCT((Event!$M$6:$M$501<=D$4)*(Event!$N$6:$N$501>=D$4)*(Event!$K$6:$K$501=$A8)*((Event!$L$6:$L$501="Vacation")+(Event!$L$6:$L$501="Sick"))),"Vac","")

 

[Dryver14]

This seems very complicated, are 495 lines of something likely to be an amount, or are you trying to get the answer this a line at a time as eachof these lines is an individual?

 

[Kevin3]

This seems very complicated, are 495 lines of something likely to be an amount, or are you trying to get the answer this a line at a time as eachof these lines is an individual?

I think I have the lines figured out. I am more focused on trying to change the value_if_true based on the selection within the sumproduct.