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"]
R
[/td]S
[/td]T
[/td]U
[/td]3
[/td](12) Active
[/td]01/03
[/td]4
[/td](12) Expired -C
[/td]07/03
[/td](12) Active
[/td]08/03
[/td]5
[/td](12) Expired -D
[/td]09/03
[/td]6
[/td]Decline
[/td]09/03
[/td]7
[/td](6) Expired -C
[/td]12/03
[/td](6) Expired -C
[/td]13/03
[/td]
[/TABLE]
Working sheet
[TABLE="class: grid, width: 500"]
K
[/td]L
[/td]21
[/td]ACTIVE
[/td]22
[/td]INACTIVE
[/td]
[/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.