Help with If and AND

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
600
Office Version
  1. 2010
Platform
  1. Windows
Hi:

Formula is P18 is returning "" and I don't know why. Formula should return "add 175"

Data range is a pivot table.

Could you help please?



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Account</td><td style=";">2010106</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Row Labels</td><td style=";">Sum of Amt</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">01/02/18</td><td style="text-align: right;;"> 125.00 </td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">02/01/18</td><td style="text-align: right;;"> 860.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">03/01/18</td><td style="text-align: right;;"> 225.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Grand Total</td><td style="text-align: right;;"> 1,210.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">P18</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$O$15="2010106",N18=DATEVALUE(<font color="Green">"01/02/18"</font>)</font>),"add 175",""</font>)</td></tr></tbody></table></td></tr></table><br />


Regards,
Sean
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Sean,

In the formula, try removing the quotation marks from around 2010106.

Cheerio,
vcoolio.
 

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
600
Office Version
  1. 2010
Platform
  1. Windows
Hi:

That works, thanks very much.

Regards,

Sean
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
Hi Sean15,

This works for me:

=IF(AND($O$15=2010106,DATEVALUE(TEXT(N18,"dd/mm/yy"))=DATEVALUE("01/02/18")),"add 175","")

Note the entry in cell O15 is numeric and the entry in cell N18 is an actual date.

HTH

Robert
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
You're welcome Sean.
I'm glad to have been able to help.
@robert:-

Good approach to the issue. Its a more robust method.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90
Top