Complicated IF statement driving me crazy

[Clelv]

Hi All

A

B

C

D

E

F

G

H

I

1

SU ID

Transfer Location

Start Date

Start Details

Date of 1st Transfer

Transfer Details

Date of 2nd Transfer

Transfer Details

End Date

2

09/01/2017

RR

05/04/2017

DC

»»»

»»»

29/04/2017

24

24

3

25/01/2017

RR

21/04/2017

DC

06/05/2017

RR

20/05/2017

15

15

4

04/01/2017

RR

10/04/2017

DC

»»»

»»»

06/05/2017

26

26

5

26/01/2017

RR

25/04/2017

DC

»»»

»»»

20/05/2017

25

25

6

18/01/2017

RR

18/04/2017

DC

»»»

»»»

13/05/2017

25

25

7

05/01/2017

RR

08/04/2017

trev

»»»

»»»

22/04/2017

14

14

8

21/03/2017

RR

30/05/2017

DC

»»»

»»»

10/06/2017

11

11

9

01/03/2017

DC

30/05/2017

RR

»»»

»»»

24/06/2017

90

0

10

02/03/2017

RR

30/05/2017

DC

»»»

»»»

09/06/2017

10

10

11

16/03/2017

DC

10/06/2017

RR

15/06/2017

DC

17/06/2017

88

88

12

30/03/2017

RR

26/06/2017

DC

»»»

»»»

Current

0

0

13

28/11/2016

RR

06/03/2017

DC

»»»

»»»

01/04/2017

26

26

14

02/05/2017

DC

»»»

»»»

»»»

»»»

01/06/2017

30

0

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

This is a table of data i've been working on. the second to end column adds up how many days the Service user required DC (DAY CARE). I somehow got there with this formula:

=IFERROR(IF(AND($I3="DC",$E3="DC"),$J3-$H3+$F3-$D3,
IF(AND($G3="DC",$I3="RR"),$H3-$F3,
IF(AND($E3="DC",$G3="RR"),$F3-$D3,
IF(AND($E3="DC",$G3="»»»"),$J3-$D3,$J3-$F3)))),0)


In the end column i'm attempting to do the same but have written the formula using <> to enable me to type text other than "DC" but get the same results as in cell F7 where i've used "trev" instead of "DC" and have been given the same outcome.

For some reason though this formula misses row 9. where i want to count the days between E9 and C9 and i just cannot get to the bottom of it and it's driving me nuts!! this is the formula i'm using. I would be really grateful if someone can spot the flaw/flaws. Thanks

=IFERROR(IF(AND($I3<>"RR",$E3<>"RR"),$J3-$H3+$F3-$D3,
IF(AND($G3<>"RR",$I3="RR"),$H3-$F3,
IF(AND($E3<>"RR",$G3="RR"),$F3-$D3,
IF(AND($E3<>"RR",$G3="»»»"),$J3-$D3,$J3-$F3)))),0)

 

[Clelv]

I think I've done it. Just added H9<>"»»»" into the and statement and it gave me the 90 I've been looking for. Amazing! I know it's slightly bodged but it's bringing back the right numbers now so I'm over the moon.

I'd never have got there without your help thanks Joe4. Saved my sanity!

 

[Joe4]

You are welcome!

Yes, people often get really confused about when to use AND or OR, especially when dealing with "not equal". It is almost like a double-negative, and isn't always clearly intuitive.

 

[Clelv]

I can't believe it, I'd just cracked it and then you sent it me too. Amazing

Thank you so so much Joe4, I know it's slightly bodged but it's returning the right count of days now so I'm absolutely over the moon and will keep what's left of my sanity haha

There's no way I'd of got this right without your help. Thanks Again.

 

[Clelv]

oops I didn't think that first one went through!