I Just cant figure this out, COUNTSIF I think!!

[ijones2932]

Hi, can anybody help with this please!

So I need a count by facilitator (Column G) when someone is coming up to or has reached 3 months (Column H) it shouldn't count after that date. So the count for IJ will be 0 as we are past that date

This would be repeated with a count by facilitator for 9 months and the count would be 5 for IJ as they are all before the 9 month mark but I just cant get my head around it!

Then I would need a count by how many are coming up to 12 months by facilitator.

I hope this make sense as I've been baffled for 2 days trying to figure this out so I've had to reach out



,

 

[ijones2932]

@Fluff I think ive done it correctly above. My formulas were on a different sheet so have copied and pasted them to Column P.

Sorry its taken a while the old laptop is very old and very slow!

 

[Fluff]

Thanks for that.
You need to refer to P10 rather than G3 so Q10 should be

=COUNTIFS('Current Cohort'!G:G,P10,'Current Cohort'!H:H,">="&TODAY(),'Current Cohort'!H:H,"<="&EDATE(TODAY(),3))

 

[ijones2932]

Thanks for that.
You need to refer to P10 rather than G3 so Q10 should be

=COUNTIFS('Current Cohort'!G:G,P10,'Current Cohort'!H:H,">="&TODAY(),'Current Cohort'!H:H,"<="&EDATE(TODAY(),3))

Thank you so much. its nearly there. I just need to figure out how to count any over 12 month old now with the same parameters. Though just by changing to 12 would work but it doesn't.

Ive changed row 10 column C to 10/05/2022 so that puts Column L row 10 over 12 months but it doesnt count it and ive change them formula to greater than

=COUNTIFS('Current Cohort'!G:G,A40,'Current Cohort'!J:J,">="&TODAY(),'Current Cohort'!J:J,">="&EDATE(TODAY(),12))

 

[Fluff]

How about

=COUNTIFS('Current Cohort'!G:G,P10,'Current Cohort'!L:L,">"&EDATE(TODAY(),12))

 

[ijones2932]

How about

=COUNTIFS('Current Cohort'!G:G,P10,'Current Cohort'!L:L,">"&EDATE(TODAY(),12))

No it just comes back as 0 but it should be 1 as ive set one over 12 months

 

[Fluff]

If I change L11 to 11/8/24 I get 1

 

[ijones2932]

If I change L11 to 11/8/24 I get 1

Ok so that would be less than 12 months. I need it to count if it goes over 12 months so it would go to 1 if the date was 09/08/2023

 

[Fluff]

That date was yesterday

 

[ijones2932]

Ok so that would be less than 12 months. I need it to count if it goes over 12 months so it would go to 1 if the date was 09/08/2023

That date was yesterday

Yes so if it's gone past the 12 month mark which with the above example was yesterday in the column that should be counted. Sorry if this doesn't make sense. My head is battered from a busy day so

 

[Fluff]

So you mean any date that is greater than yesterday?