[Solved] Week/Supervisor wise attendance

[Vasimkhan]

Please help me with the Excel formula to get week wise supervisor level count of attendance and leave in the yellow highlighted cells. Attached is the excel file with the sample raw data.

 

[Yousuf Shovon]

Please help me with the Excel formula to get week wise supervisor level count of attendance and leave in the yellow highlighted cells.

Hello VasimKhan,

Welcome to ExcelDemy Forum! I understand you want a formula to get week wise supervisor level count of attendance and leave it in the yellow highlighted cells. Reviewing your dataset, I think we can create a formula. But, you did not specify how dynamic you want this to be.

Assuming you want to automatically change range reference when a week is input to cell B11, first input the following formula in I11 and J11.

• In I11,

=CHOOSE(RIGHT(B11,1),"D","I","N","S","X") & 4

• In J11,

=CHOOSE(RIGHT(B11,1),"H","M","R","W","X") & 9

• As a result, you can make the template dynamic week wise.

• Value changes if you change the week number.​

• Now, input the formula in C14 to get the present number:

=SUMPRODUCT(ISNUMBER(MATCH($C$4:$C$9,$B14,0))*ISNUMBER(MATCH(INDIRECT($I$11 & ":" & $J$11),{"P","PNS"},0)))

​

• Replicate this formula to other cells simultaneously. Then, for week 1, the output is:

​

• And for week 2, the output is:

​

See Excel COUNTIFS with Multiple Criteria and OR Logic article if you want to extend your template further.

I have attached the Excel workbook for your understanding. Let me know if it works. Thank you.

Regards,
Yousuf Shovon

 

[EmilXmulo]

Thanks for sharing the solution. The dynamic week range setup is useful, especially when the same report needs to cover multiple supervisors. The SUMPRODUCT formula also looks like a good way to handle the Present and PNS conditions without manually counting each section. For anyone who only needs a quick way to work out attendance percentages, an attendance calculator is also worth checking out.

 

[shamimarita]

Hello EmilXmulo,

Thank you for your feedback! We’re glad you found the dynamic week setup and the SUMPRODUCT formula useful for handling supervisor-wise attendance reports.

If you need any further modifications or want to make the report more dynamic, feel free to let us know.