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.
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: