1

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.

0

based on your question I assume your data looks like:

where you can drop the whole side calculations by having:

  • cell D10:
    =SUM(SUMPRODUCT(OR(COUNTIF(B3;"<>"); COUNTIF(B4;"<>")));
         SUMPRODUCT(OR(COUNTIF(B5;"<>"); COUNTIF(B6;"<>")));
         SUMPRODUCT(OR(COUNTIF(B7;"<>"); COUNTIF(B8;"<>"))))
  • or cell B10:
    =SUM(IF(OR(D3<>""; D4<>""); 1;);
         IF(OR(D5<>""; D6<>""); 1;);
         IF(OR(D7<>""; D8<>""); 1;))


enter image description here enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.