I have a spreadsheet that operates as a roster and I want to work out coverage. So each column represents a shift and each pair of rows represents a day because each day has two slots available.
To explain what I mean, consider this table:
| Col1 | Col2 | Col3 | |----------|---------|---------| | John | John | | | Bob | | | | -------- | ------- | ------- | | John | | | | Bob | | John | | -------- | ------- | ------- | | John | | | | | Bob | | | -------- | ------- | ------- | | -------- | ------- | ------- | | 3 | 2 | 1 |
Each name is on a different row (the —- separator is just for clarity). I want to first check if a string exists in each pair of rows (to indicate it has been covered) and then the bottom row represents how many of the days have coverage.
Currently, I just do a =IF(OR(C3<>"", C4<>""), 1, 0)
, =IF(OR(C5<>"", C6<>""), 1, 0)
, etc. for each pair in a separate column and then sum it at the bottom but I feel that there has to be a better way.
Best Answer
based on your question I assume your data looks like:
where you can drop the whole side calculations by having: