Leave tracker issues - formula written but has an error

[steve89]

=SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],I_RPT_EMP,LeaveTracker[Start Date],">="&DATE(I_RPT_YR,1,1),LeaveTracker[End Date],"<"&DATE(I_RPT_YR+1,1,1),LeaveTracker[Type of Leave],'Leave Types'!b4)

this is the formula im using.

"leave tracker" is B4-F126 which reference the following "employee Name," "start date," "end date," "type of leave," & "days"

but for some reason its not reading it correctly and telling me im missing a comma or something but this works on another template i have. Please help or suggestions would be appreciated

 

[sgroath]

What specifically are you getting as an error or are you net getting the results you think you should? I built out a sample and the formula works fine. Your final variable of 'Leave Types'!b4 does not fit how you have defined names for your other variables and your formula does not account for leaves starting in one year and ending in another but other than that it looks OK.

Might be able to help if you provide more context.

 

[steve89]

thank you i solved this already but now im at a point where having another issue see below - and thank you for replying and trying to help.

B4 = employee name
C4 = Start Date
D4 = End Date
E4 = Type of Leave
F4 = Days
G4 = Rate of Increase

i created an attendance/infraction/ paid time off sheet and use B4:G126 which is called T_Leave. i use that to enter information and the sheet keeps it for years. My sheet has rollover years so i can change the year to 2019 and whatever i enter on 2018 wont be there unless i switch back. Runs great but the formula i have (see below) works with everything but for some reason it wont give me the "rate of increase" even though that is part of the T_Leave chart name where everything is referenced.

=IFERROR(IF(MONTH(DATE(I_RPT_YR,$A12,C$11))<>$A12,"NA",IF(DATE(I_RPT_YR,$A12,C$11)>I_RPT_ED,"NA",IF(DATE($B$4,$A12,C$11)<$AL$3,"NE",IF(AND($BL$3>0,DATE($B$4,$A12,C$11)>$BL$3),"NE",IF(NOT(ISERROR(MATCH(DATE($B$4,$A12,C$11),L_HOLS,0))),"H",IF(INDEX(L_WKNDVAL,WEEKDAY(DATE($B$4,$A12,C$11),1))=1,"WKND",INDEX(T_LEAVE[rate of increase],SUMPRODUCT(--(T_LEAVE[EMPLOYEE NAME]=I_RPT_EMP),--(T_LEAVE[START DATE]<=DATE($B$4,$A12,C$11)),--(T_LEAVE[END DATE]>=DATE($B$4,$A12,C$11)),ROW(T_LEAVE[rate of increase]))-ROW(T_LEAVE[#Headers])))))))),"")

I_RPT_YR = the year i chose to see my data
A12 = the numbered month
C11 = the day of the month

now all my enter data lets say i entered 5 call outs, 6 vacations and 3 write ups for john doe in 2018 that would all appear on that employees report. But i want the sheet to tell me the rate of the increase and its not coming up just showing a blank cell with no errors. bit confusing sorry