[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