| B | C | D | E |
---|
Name | Available | Absent | Vacation | |
Jone | | | | |
Jack | | | | |
Bob | | | | |
David | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
</tbody>
M1
| B | C | D | E |
---|
Name | Available | Absent | Vacation | |
Jone | | | | |
Jack | | | | |
Bob | | | | |
Samir | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
</tbody>
M2
| B | C | D | E |
---|
Name | Available | Absent | Vacation | |
Jone | | | | |
Jack | | | | |
Bob | | | | |
Samir | | | | |
Anderson | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
</tbody>
M3
| B | C | D | E |
---|
Name | Available | Absent | Vacation | |
Jack | | | | |
Bob | | | | |
Samir | | | | |
Anderson | | | | |
David | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]17[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
</tbody>
M4
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #5B9BD5"]Name[/TD]
[TD="bgcolor: #5B9BD5"]Available[/TD]
[TD="bgcolor: #5B9BD5"]Absent[/TD]
[TD="bgcolor: #5B9BD5"]Vacation[/TD]
[TD="align: center"]7[/TD]
[TD="bgcolor: #DDEBF7"]Jone[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28[/TD]
[TD="align: center"]9[/TD]
[TD="bgcolor: #DDEBF7"]Bob[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]32[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]27[/TD]
[TD="align: center"]11[/TD]
[TD="bgcolor: #DDEBF7"]Anderson[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]25[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
</tbody>
Summary
In C7 of Summary enter, copy across, and down:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B7:B30"),$B7,INDIRECT("'"&SheetList&"'!"&CELL("address",C$7:C$30))))
where SheetList is the name of a range which houses the relevant sheet names, that is, M1, M2, M3, and M4.