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



,

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(G:G,G3,H:H,">="&TODAY(),H:H,"<="&EDATE(TODAY(),3))

 

[ijones2932]

Hi & welcome to MrExcel.
How about

=COUNTIFS(G:G,G3,H:H,">="&TODAY(),H:H,"<="&EDATE(TODAY(),3))

Thank you but I'm getting 0 back as the count but that could be because the 3 month one and all are over 3 month so i've changed the rows to reflect the 9 month column and still getting 0 but I also need to count by facilitator i.e IJ SG etc from column G

=COUNTIFS(G:G,G3,J:J,">="&TODAY(),J:J,"<="&EDATE(TODAY(),3))

 

[Fluff]

For the 9months you need to change the 3 in the Edate function to 9

 

[ijones2932]

For the 9months you need to change the 3 in the Edate function to 9

Sorry I did that as I realized but it still came back as 0

 

[Fluff]

In that case can you post some sample data that includes the formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ijones2932]

In that case can you post some sample data that includes the formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I really do appreciate your help, unfortunately I cant install the add in as its a work computer! I will see if I can install via my phone

 

[Fluff]

You will not be able to use it on a phone as it uses VBA.

If you select col J & change the format to General what do you see?

 

[ijones2932]

You will not be able to use it on a phone as it uses VBA.

If you select col J & change the format to General what do you see?

I will have to drag the old laptop out. thank you for your help

 

[ijones2932]

You will not be able to use it on a phone as it uses VBA.

If you select col J & change the format to General what do you see?

Test Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Surname	First Name	Induction Date	Ward	Site	Line Manager	Facilitator	3 Month	1st Meeting Booked	9 Month	2nd Meeting Booked	12 Month	3rd Meeting Booked	Care Certificate Progress	Additional Notes
3	Test	Test	03/11/2022	Mulberry Ward	Park House	Georgia Jackson	IJ	03/02/2023		03/08/2023		03/11/2023
4	Test	Test	04/11/2022	Delaney Ward	The Lowry	Joseph Ogbeide	IJ	04/02/2023		04/08/2023		04/11/2023
5	Test	Test	05/11/2022	JDU	JDU	Lindsay Quar	SG	05/02/2023		05/08/2023		05/11/2023
6	Test	Test	06/11/2022	Silverdale Ward	Edenfield Unit	Jenny Hodgkiss	IJ	06/02/2023		06/08/2023		06/11/2023
7	Test	Test	07/11/2022	Gardener Unit	CAHMS	Katie Coffey	IJ	07/02/2023		07/08/2023		07/11/2023
8	Test	Test	08/11/2022	Salford Bramley St	Salford Bramley St	Nigel Figgins	JM	08/02/2023		08/08/2023		08/11/2023				Summary Sheet 3 Month Caseload
9	Test	Test	09/11/2022	Silverdale Ward	Edenfield Unit	Jenny Hodgkiss	IJ	09/02/2023		09/08/2023		09/11/2023				Name	Amount
10	Test	Test	10/11/2022	Wentworth House	Wentworth House	Memory Duxbery	AM	10/02/2023		10/08/2023		10/11/2023				AM	3
11	Test	Test	03/07/2023	Bronte Ward	Laureate House	Christabel Lamptey	AM	03/10/2023		03/04/2024		03/07/2024				IJ	3
12	Test	Test	03/07/2023	Maple Ward	Park House	Danielle Hardy	IJ	03/10/2023		03/04/2024		03/07/2024				SG	3
13	Test	Test	03/07/2023	Prospect Ward	Atherleigh Park	Charlotte Hammond	AM	03/10/2023		03/04/2024		03/07/2024				JM	3
14	Test	Test	03/07/2023	Cavendish Ward	Laureate House	Joanna Pease	SG	03/10/2023		03/04/2024		03/07/2024
15	Test	Test	03/07/2023	Rydal Ward	Edenfield Unit	Adasa Dixon	JM	03/10/2023		03/04/2024		03/07/2024
16	Test	Test	03/07/2023	Ullswater Ward	Edenfield Unit	Andrew Woodward	JM	03/10/2023		03/04/2024		03/07/2024				Summary Sheet 9 Month Caseload
17	Test	Test	03/07/2023	Phoenix Ward	Junction 17 Unit	Molly Farebrother	JM	03/10/2023		03/04/2024		03/07/2024				Name	Amount
18	Test	Test	03/07/2023	Mulberry Ward	Park House	Georgia Jackson	JM	03/10/2023		03/04/2024		03/07/2024				AM	3
19	Test	Test	03/07/2023	A&E Liaison (Central)	MRI	Chelsea Drake	JM	03/10/2023		03/04/2024		03/07/2024				IJ
20	Test	Test	03/07/2023	Laurel Ward	Park House	Jo Ann Jager	SG	03/10/2023		03/04/2024		03/07/2024				SG
21	Test	Test	03/07/2023	Hayeswater Ward	Edenfield Unit	Lucy Kenyon	IJ	03/10/2023		03/04/2024		03/07/2024				JM
22	Test	Test	03/07/2023	Hayeswater Ward	Edenfield Unit	Lucy Kenyon	SG	03/10/2023		03/04/2024		03/07/2024
23	Test	Test	26/06/2023	Cavendish Ward	Laureate House	Joanna Pease	AM	26/09/2023		26/03/2024		26/06/2024
24	Test	Test	19/06/2023	Ullswater Ward	Edenfield Unit	Andrew Woodward	IJ	19/09/2023		19/03/2024		19/06/2024
25	Test	Test	12/06/2023	Honeysuckle Lodge	Rivington Unit	Imogen Naz	JM	12/09/2023		12/03/2024		12/06/2024
26	Test	Test	06/06/2023	Blake Ward	Laureate House	Louise Rosario Tulloch	JM	06/09/2023		06/03/2024		06/06/2024
27	Test	Test	06/06/2023	Bronte Ward	Laureate House	Christabel Lamptey	JM	06/09/2023		06/03/2024		06/06/2024
28	Test	Test	06/06/2023	Keats Ward	Meadowbrook Unit	Keeley Oats	JM	06/09/2023		06/03/2024		06/06/2024
29	Test	Test	06/06/2023	Delaney Ward	The Lowry	Joseph Ogbeide	JM	06/09/2023		06/03/2024		06/06/2024
Current Cohort

Cell Formulas
Range	Formula
E3:E29	E3	=XLOOKUP([@Ward],'Formula Data'!F:F,'Formula Data'!G:G)
F3:F29	F3	=XLOOKUP([@Ward],'Formula Data'!F:F,'Formula Data'!H:H)
Q10:Q13	Q10	=COUNTIFS('Current Cohort'!G:G,'Current Cohort'!G3,'Current Cohort'!H:H,">="&TODAY(),'Current Cohort'!H:H,"<="&EDATE(TODAY(),3))
Q18	Q18	=COUNTIFS('Current Cohort'!G:G,'Current Cohort'!G3,'Current Cohort'!J:J,">="&TODAY(),'Current Cohort'!J:J,"<="&EDATE(TODAY(),9))
H3:H29	H3	=EDATE(C3,3)
J3:J29	J3	=EDATE(C3,9)
L3:L29	L3	=EDATE(C3,12)

Cells with Data Validation
Cell	Allow	Criteria
D3:D29	List	='Formula Data'!$F$2:$F$71
P9	Any value
Q9	Any value
P17	Any value
Q17	Any value
N3:N29	List	='Formula Data'!$A$8:$A$11
G3:G29	List	='Formula Data'!$B$7:$B$11