Help with Nested IF

[cdnqte]

Hi All,

I am struggling with an issue that I am sure is simple to resolve and I'm just missing the obvious so I'm hoping someone can help!

I have the following information in my spreadsheet

CRITERIA$b$1 = MONTH END DATE
RETURN DATE = DATE UNIT WAS RETURNED
BILLED THRU = BILLED THRU DATE
A/D = ACCRUE OR DEFER

What I NEED it to do is the following:

If RETURN DATE >= CRITERIA$B$1 AND A/D=D THEN SUM CRITERIA$B$1-BILLED TRHU *-1
IF RETURN DATE <CRITERIA$B1 THEN RETURN 0

I tried the following but it sets everything to 0
=IF([@[Return Date]]<=Criteria!$B$1,0,IF([@[A/D]]="D",SUM(Criteria!$B$1-[@[Billed Thru]]),0)*-1)

I also tried this but it is calculating values for those returned before the value in the CRITERIA$B$1 field and ignoring those with blank dates
=IF([@[Return Date]]>=Criteria!$B$1,0,IF([@[A/D]]="D",SUM(Criteria!$B$1-[@[Billed Thru]]),0)*-1)


Also, I need to make sure that if the return date is blank that it acts the same way as the scenario where the return date is > criteria$B$1



I am horrible with nested ifs so ANY HELP would be greatly appreciated!!

Thanks!

 

[KUYJS]

Try this and use your cell reference

=if(and(ReturnDate>=Criteria$B$1,A/D="D"),(Criteria$B$1-BilledThru)*(-1),RerurnDate)