Google-sheets – Check whether a value exists in either cell in a pair of rows for a whole column

google sheets

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:

  • 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