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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Completed</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">No</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">B2:OFFSET(<font color="Purple">A2,0,A2</font>)</font>)</font>)/A2=1,"Yes","No"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">B3:OFFSET(<font color="Purple">A3,0,A3</font>)</font>)</font>)/A3=1,"Yes","No"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I4</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">B4:OFFSET(<font color="Purple">A4,0,A4</font>)</font>)</font>)/A4=1,"Yes","No"</font>)</td></tr></tbody></table></td></tr></table><br />
 

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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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