Formula Contradiction

[Irwell]

Morning,

I created this formula (see below) yesterday and I've just realised that it's not working and I've figured out the reason... How would you change it?
=COUNTIFS('Tier 3 Tracker'!$F:$F,"<=30/09/2017",'Tier 3 Tracker'!$Z:$Z,">=12")-COUNTIF('Tier 3 Tracker'!$AT:$AT,"<30/06/2017")

Basically I want it count all those in column F, minus those in column AT then tell me how many of those that are left are over 12... (Does that make sense)

 

[steve the fish]

Its difficult to know but it could be:

=COUNTIFS('Tier 3 Tracker'!$F:$F,"<=30/09/2017",'Tier 3 Tracker'!$Z:$Z,">=12",'Tier 3 Tracker'!$AT:$AT,">30/06/2017")

 

[Irwell]

Its difficult to know but it could be:

=COUNTIFS('Tier 3 Tracker'!$F:$F,"<=30/09/2017",'Tier 3 Tracker'!$Z:$Z,">=12",'Tier 3 Tracker'!$AT:$AT,">30/06/2017")

That wouldn't count any of those in AT that are blank so that wouldn't work I don't think, I'll test it now.

 

[Irwell]

Think I'm being thick again to be honest.

Basically I want the below...

Countif('Tier 3 Tracker'!$Z:$Z,">=12")

Count those over 12 in column Z

But only count from the below people
=COUNTIF('Tier 3 Tracker'!$F:$F,"<=30/09/2017")-COUNTIF('Tier 3 Tracker'!$AT:$AT,"<=30/06/2017")

F is people who have a start date and AT is people who have discharged.

 

[steve the fish]

Im not at all clear what you are asking for. Could you say in words what you want? Either that give a representative data sample with expected results.

 

[Irwell]

Im not at all clear what you are asking for. Could you say in words what you want? Either that give a representative data sample with expected results.

Thank you for your help, I'll do my best to explain.

I have 3 columsn on sheet 1

F = Start Dates
Z = Weeks since last review
AT = Discharge Dates

I want to know for each quarter how many people are over 12 weeks since last review. To do that manually I'd filter on all those that have started before the last day of the quarter and filter out anyone that discharged prior to the quarter starting. I need that putting in a formula, does that make more sense?

Thanks,

Simon

 

[inactiveuserps07]

@Irwell, I'm wondering if this might be an easier way:

=SUMPRODUCT(([COLOR=#333333]'Tier 3 Tracker'!$F:$F<=30/09/2017)*(ISERROR(1/[/COLOR][COLOR=#333333]'Tier 3 Tracker'!$AT:$AT))*([/COLOR][COLOR=#333333]'Tier 3 Tracker'!$Z:$Z>=12))

My thinking on the AT column is that if any date is there, then the person is discharged and so it doesn't matter when they last had a review. So the middle of the above formula will assure that only records/rows with no discharge date are counted.

Likewise, you wouldn't seem to need to count only the last 90 days' worth of late-review people still working there. For instance, if someone hadn't had a review in 20 weeks (theoretically), it doesn't really matter what quarter the lapse happened in: they need a review.[/COLOR]

 

[Irwell]

@Irwell, I'm wondering if this might be an easier way:

=SUMPRODUCT(([COLOR=#333333]'Tier 3 Tracker'!$F:$F<=30/09/2017)*(ISERROR(1/[/COLOR][COLOR=#333333]'Tier 3 Tracker'!$AT:$AT))*([/COLOR][COLOR=#333333]'Tier 3 Tracker'!$Z:$Z>=12))[/COLOR]

My thinking on the AT column is that if any date is there, then the person is discharged and so it doesn't matter when they last had a review. So the middle of the above formula will assure that only records/rows with no discharge date are counted.

Likewise, you wouldn't seem to need to count only the last 90 days' worth of late-review people still working there. For instance, if someone hadn't had a review in 20 weeks (theoretically), it doesn't really matter what quarter the lapse happened in: they need a review.

Due to formulas in column Z it's now pulling them through so returning 998?

 

[inactiveuserps07]

Hi, Simon. I know it's hard to keep in mind, but we can't see what you see, including "formulas in Column Z." You know what you mean by "pulling them through," but we don't, nor whether 998 is a reasonable answer given your data. If you'd like, try explaining and giving examples or desired outcomes, all the while thinking, "What could people misunderstand this to mean if they couldn't see what I'm seeing?" and then adjusting accordingly.

 

[steve the fish]

Presuming your weeks since last review column is correct and column AT is only filled in when someone leaves this should work:

=COUNTIFS(Z:Z,">12",AT:AT,"")