# If and or...

#### John BX

##### New Member
 Days R1 R2 R3 R4 R5 R6 R7 Completed 3 1 7

<tbody>
</tbody>

Hi,

Im trying to figure out the formula to go in the "Completed" column in my sheet above, which should show "Yes" or "No".

The Days column can contain a number between 1 and 7.
The first row of the days column has the value 3, so I want to have the Completed column to show "Yes" if R1,R2 and R3 *all* contain any value, otherwise it should show "No".

Likewise the 2nd row in the days column has the value 1 so only R1 needs to have a value to show "Yes" in the Completed column.
again the 3rd days row has 7 so all 7 R columns need a value to show "Yes".

Thanks

John

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### shobolos

##### Active Member

Excel 2010
I
1Completed
2Yes
3Yes
4No
Sheet1
Cell Formulas
RangeFormula
I2=IF(SUMPRODUCT(--ISNUMBER(B2:OFFSET(A2,0,A2)))/A2=1,"Yes","No")
I3=IF(SUMPRODUCT(--ISNUMBER(B3:OFFSET(A3,0,A3)))/A3=1,"Yes","No")
I4=IF(SUMPRODUCT(--ISNUMBER(B4:OFFSET(A4,0,A4)))/A4=1,"Yes","No")

#### Claus8528

##### New Member
There might be a smarter way but this works for me:

Assuming that Days is in A1 etc.
Code:
``=IF(A2=0;"",IF(COUNTA(OFFSET(A2,,1,,A2))=A2,"YES","NO"))``

#### John BX

##### New Member
Thanks, that did exactly the trick!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,671
Messages
5,838,691
Members
430,564
Latest member
Raeyven

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back