Offset formulas

[no_Fate]

Hi everyone,

I’m using Sumit Bansal’s Leave Tracker Template since it has a lot of features that I was looking for.

Still, I’ve added 3 columns on the left to identify each person’s team leader (TL), task and shift.
I’ve already managed to work on the macro so it keeps hiding the correct amount of columns.

Now, what I’m not being able to adapt myself is the way the formulas count the month and year's absences and worked days. I can’t understand all these offsets

All I’m missing is now is the math:

Leaves This Month: should count all the “JA”, “UA”, “P”, “SL” as 1 and “H1” as 0.5
WorkDays This Month: should count all the “T”, “L1”, “1” as 1 and “H1” as 0.5
Year Leave Breakup: Should simply count the instances each code occurs for the entire year.


Would someone help me out with this, please?
Thank you.

[Bo_Ry]

Please try
swap H1 to row 16

NM7
=SUMPRODUCT(COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$7:$OA$10))+COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$15)/2

NN7
=SUMPRODUCT(COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$13:$OA$15))+COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$16)/2

NO7:NX7
=COUNTIF($E7:$NL7,NO$5)

[no_Fate]

Please try
swap H1 to row 16

NM7
=SUMPRODUCT(COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$7:$OA$10))+COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$15)/2

NN7
=SUMPRODUCT(COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$13:$OA$15))+COUNTIF(OFFSET($A7,0,31*($A$3-1)+4,1,31),$OA$16)/2

NO7:NX7
=COUNTIF($E7:$NL7,NO$5)

Hi Bo_Ry,

Thank you so much! This is working like a charm!