Following on from a question I asked a few days ago, I need to calculate the below
I have a control Table, indicating whether each Team should be included in the results
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I then have a register of each user, and wether they were in or not on a particular day
<colgroup><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>
So I'd like to have a formula to calculate the volume of "In" values (for each row, column and overall) Only for teams where the Team Name in the control table has a value of "Yes" for the Include column.
Can anyone help?
I have a control Table, indicating whether each Team should be included in the results
A | B | |
1 | Team | Include |
2 | Team Alpha | Yes |
3 | Team Bravo | No |
4 | Team Charlie | Yes |
5 | Team Delta | No |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I then have a register of each user, and wether they were in or not on a particular day
A | B | C | D | E | F | G | E | |
1 | Agent Name | Team | 01/01/2018 | 02/01/2018 | 03/01/2018 | 04/01/2018 | 05/01/2018 | Total |
2 | John | Team Bravo | In | In | 0 | |||
3 | Mary | Team Alpha | In | In | In | In | 4 | |
4 | Kate | Team Delta | In | In | In | In | In | 0 |
5 | Brian | Team Bravo | In | In | In | 0 | ||
6 | Mark | Team Alpha | In | In | In | In | 3 | |
7 | Sarah | Team Charlie | In | In | In | In | In | 5 |
8 | Total | 2 | 2 | 2 | 3 | 3 | ||
9 | ||||||||
10 | Overall Total | 12 |
<colgroup><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>
So I'd like to have a formula to calculate the volume of "In" values (for each row, column and overall) Only for teams where the Team Name in the control table has a value of "Yes" for the Include column.
Can anyone help?