Posts by Wobzy

    Hi Guys,


    This is a grab from the actual sheet.


    I'm trying to formulate cells R and T from source sheet into L22 on working sheet.


    Source sheet (label; TRACKER)
    [TABLE="class: grid, width: 500"]

    [tr]


    [td][/td]


    [td]

    R

    [/td]


    [td]

    S

    [/td]


    [td]

    T

    [/td]


    [td]

    U

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    (12) Active

    [/td]


    [td]

    01/03

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    (12) Expired -C

    [/td]


    [td]

    07/03

    [/td]


    [td]

    (12) Active

    [/td]


    [td]

    08/03

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    (12) Expired -D

    [/td]


    [td]

    09/03

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Decline

    [/td]


    [td]

    09/03

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    (6) Expired -C

    [/td]


    [td]

    12/03

    [/td]


    [td]

    (6) Expired -C

    [/td]


    [td]

    13/03

    [/td]


    [/tr]


    [/TABLE]


    Working sheet
    [TABLE="class: grid, width: 500"]

    [tr]


    [td][/td]


    [td]

    K

    [/td]


    [td]

    L

    [/td]


    [/tr]


    [tr]


    [td]

    21

    [/td]


    [td]

    ACTIVE

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    22

    [/td]


    [td]

    INACTIVE

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]



    L21 is simply the total of values equal to active in range R:R and T:T


    There is no disqualifier to an active value. I have this one working.


    =COUNTIF(TRACKER!R:R,"*ACTIVE*")+COUNTIF(TRACKER!T:T,"*ACTIVE*")


    L22 needs to be the total of values not equal to active in R:R and T:T.


    This is where the fun begins.


    if T:T != active & is not blank then count = 1
    if R:R != active & T:T != active then count = 1
    if R:R != active & T:T = active then count = 0


    So in real terms, what i am trying to establish is this.


    Where R = new client contract
    And T = client recontract


    How many clients are no currently contracted?


    I only want to count the out of contract cell in R, if there is no corresponding recontract cell in T.


    Help is much appreciated.