Vasimkhan25
New Member
Please help me with the Excel formula to get week wise, supervisor level count of attendance and leave in the yellow highlighted cells (on change of week number). Excel sheet is attached with sample data and more details.
Week wise supervisor level attendance
- Thread starter Vasimkhan25
- Start date
Try this old school formula for all Excel versions.
In D14, formula copy across and down:
=SUM(COUNTIF(OFFSET(INDEX($A:$A,MATCH($B14,$C:$C,0)),,MATCH($B$11,$1:$1,0)-1,2,5),OFFSET($B$20,COLUMN(A:A)*2-2,,2)))
Remark: It is a dynamic formula. Click B11 dropdown list to change the week number, all results will be changed in accordingly.
In D14, formula copy across and down:
=SUM(COUNTIF(OFFSET(INDEX($A:$A,MATCH($B14,$C:$C,0)),,MATCH($B$11,$1:$1,0)-1,2,5),OFFSET($B$20,COLUMN(A:A)*2-2,,2)))
Remark: It is a dynamic formula. Click B11 dropdown list to change the week number, all results will be changed in accordingly.
Last edited: