Week/Supervisor Wise Attendance (1 Viewer)

[BowDan]

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

 

[June7]

How dynamic do you want this to be?

Example formula to use in cell C14: =SUM(COUNTIFS($D$4:$H$5,{"P","PNS"}))

More info https://exceljet.net/formulas/countifs-with-multiple-criteria-and-or-logic

Unfortunately, the two range/criteria pairs array formula won't with multi-column range so this fails: =SUM(COUNTIFS($C$4:$C$9, {"Domnik"},$D$4:$H$9,{"P";"PNS"}))

Ah, this works: =SUMPRODUCT(ISNUMBER(MATCH($C$4:$C$9,$B14,0))*ISNUMBER(MATCH($D$4:$H$9,{"P","PNS"},0)))

If you want coding to automatically change range reference when a week is input to cell B11, that will get complicated. Here is one way:

Calculations in cells I11 and J11:
=CHOOSE(RIGHT(B11,1),"D","I","N","S","X") & 4
=CHOOSE(RIGHT(B11,1),"H","M","R","W","X") & 9

Then formula in C14:
=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 and adjust for the code constants.

 

[BowDan]

Ah, this works: =SUMPRODUCT(ISNUMBER(MATCH($C$4:$C$9,$B14,0))*ISNUMBER(MATCH($D$4:$H$9,{"P","PNS"},0)))

If you want coding to automatically change range reference when a week is input to cell B11, that will get complicated. Here is one way:

Calculations in cells I11 and J11:
=CHOOSE(RIGHT(B11,1),"D","I","N","S","X") & 4
=CHOOSE(RIGHT(B11,1),"H","M","R","W","X") & 9

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

Well, this turns out better than I expected. Thanks a lot for all the efforts you have put into this.

Best Wishes.