If and or...

John BX

New Member
Joined
Dec 22, 2014
Messages
36
DaysR1R2R3R4R5R6R7Completed
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
Joined
Sep 7, 2010
Messages
274

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
Joined
Nov 1, 2014
Messages
47
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"))
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top