A | B | C | D | E | F | G | H | I | J | K | L | M | N | F | |
1 | Name | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
2 | John | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF | OFF | OFF | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM |
3 | David | 8 AM | 8 AM | 8 AM | OFF | OFF | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF |
4 | Eve | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF |
5 | |||||||||||||||
6 | Name | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
7 | John | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF | 8 AM | 8 AM | OFF | 8 AM | 8 AM | OFF | 8 AM |
8 | Rose | OFF | OFF | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF | 8 AM | 8 AM |
9 | Eve | 8 AM | 8 AM | 8 AM | OFF | OFF | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | 8 AM | OFF | OFF |
<tbody>
</tbody>
I have this badly designed table and I'm trying to calculate how many times people were off on a certain day of the week.
Countif struggles with blank rows (row 5) or ranges which are not equal.
So I want to create a table Monday to Sunday and calculate how many times employees were off on certain days. Note that this schedule is based on 2weeks range (B-F)
Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |
Eve | 0 | 0 | 0 | 1 | 1 | 3 | 3 |
<tbody>
</tbody>
Thanks