IF AND based on condition of another cell

[Delta729]

I'm attempting to create an IF AND Statement which would let me know if there is a duplicate Time Type "SB" for a given name and date. The formula I have right now is not working and any help would be greatly appreciated.

=IF(P3="SB",IF(AND(N3=N2,O3=O2),"Duplicate","OK"))

<TABLE style="WIDTH: 422pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=563 border=0 x:str><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" width=186><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" span=2 width=67><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 32.25pt; mso-height-source: userset" height=43><TD class=xl26 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 32.25pt; BACKGROUND-COLOR: #003366" width=186 height=43>Date Worked</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #003366" width=123>Name</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #003366" width=35>Time Type</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #003366" width=67>Gross Price</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #003366" width=67>Net Value</TD><TD class=xl27 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 64pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=85>Single
SB Verification</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl31 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=186 height=18 x:num="40181">Sunday, January 03, 2010</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=123>Bill Soderquist</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>NT</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="111.76">$111.76</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="223.52">$223.52</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl30 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ffff99" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl29 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=123>Acosta, Charles</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>IN</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="81.05">$81.05</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="162.1">$162.10</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(C3="SB",IF(AND(A3=A2,B3=B2),"Duplicate","OK"))' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl30 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ffff99" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl29 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=123>Acosta, Charles</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>ML</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="15">$15.00</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="30">$30.00</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(AND(A4=A3,B4=B3),"Duplicate","OK")'>Duplicate</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl30 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ffff99" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl29 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=123>Acosta, Charles</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>PT</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="162.09">$162.09</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="1458.81">$1,458.81</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(AND(A5=A4,B5=B4),"Duplicate","OK")'>Duplicate</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl30 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ffff99" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl29 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=123>Acosta, Charles</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>PT</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="162.09">$162.09</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="1053.59">$1,053.59</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(AND(A6=A5,B6=B5),"Duplicate","OK")'>Duplicate</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl30 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ffff99" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl29 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=123>Acosta, Charles</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>SB</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="50">$50.00</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="50">$50.00</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(AND(A7=A6,B7=B6),"Duplicate","OK")'>Duplicate</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl31 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=186 height=18 x:num="40483">Monday, November 01, 2010</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 92pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=123>Armenta, Robert</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 26pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=35>NT</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="93.16">$93.16</TD><TD class=xl25 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 50pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=67 x:num="745.28">$745.28</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:fmla='=IF(AND(A8=A7,B8=B7),"Duplicate","OK")'>OK</TD></TR></TBODY></TABLE>

Date Worked = column N and no missing columns.

Any help would be greatly appreaciated.

 

[Murali1881]

Try this formula on Cell S3 & Drag it

=IF(AND(N4=N3,O4=O3),IF(AND(P2=P3),"Duplicate","OK"),"OK")

Thanks & Regards,

Murali
<!-- / message -->

 

[Delta729]

Thanks for your help... that worked perfectly. I guess I was too tired last night when trying to add the formula, I was just pasting it in and forgot to adjust the columns to a new column which was added. OK, I'm an idiot.
Thanks again.