Formula Help Please

[HAYLO22]

I have a spreadsheet which counts when clients have been removed from a scheme, if they have completed and how many sessions the client completed within particular time periods.
I have formulas which work out how many clients have been removed/completed within each month etc but I cannot figure out how to collate the data with regards to the amount of sessions have been completed for each client that have completed/been removed within a particular time frame.

INSTRUCTOR

TOTAL REMOVED CLIENTS

TOTAL COMPLETED CLIENTS

TOTAL REFERRED

Leigh

1​

2​

0​

Below is the formula for clients 'completed' in March 2023 which was 2.

=COUNTIFS(HOP!E2:E5000,"Leigh",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
How do I add the total number of sessions completed for the 2 clients in this formula please?
Sessions completed is on the tab named HOP and is in the column M2-M5000

Please let me know if you need more info.

 

[awoohaw]

I think this gets what you want:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Client Name	Card No.	Medical Condition	Referral Date	Instructor	Status	Initial Appointment	Last Appointment	Next Appointment	Date Removed	Chase Up	Notes	Sessions Completed	Notes Completed	Expiry Date	Date Checked	INCOME FOR NOTES COMPLETED	Testimonials
3	Joe Bloggs			05/07/2022	Leigh	Completed	2022-08-18	2023-03-01					23	23	01/03/2023	01/03/2023	£598.00
4	Jill Bloggs			04/10/2022	Leigh	Completed	2022-10-22	2023-03-01					14	14	26/04/2023	12/04/2023	£364.00
5
6	HOP
7	INSTRUCTOR	TOTAL REMOVED CLIENTS	TOTAL COMPLETED CLIENTS	TOTAL REFERRED	TOTAL NOTES	sessions completed
8	Leigh	1	2	0		37
9
10	B104	C104
11	2023-03-01	2023-03-31
HAYLO22

Cell Formulas
Range	Formula
C8	C8	=COUNTIFS($E$3:$E$4,$A8,$F$3:$F$4,"Completed",$H$3:$H$4,">=" & $A$11,$H$3:$H$4,"<=" & $B$11)
F8	F8	=SUMIFS($M$3:$M$4,$E$3:$E$4,$A8,$F$3:$F$4,"Completed",$H$3:$H$4,">=" & $A$11,$H$3:$H$4,"<=" & $B$11)

 

[HAYLO22]

Thank you so much, I will try that out on my spreadsheet when I get home. Thanks for taking the time to help me. I will let you know how I get on.

 

[HAYLO22]

Okay, so I don' think I added the clients sessions. Where is that data? Can you post some? And what the expected result is? I think I may have the question figured. hold on....

Thank you - sorry I can't see where the total number of notes are? It's so hard to explain without showing my spreadsheet.

Perhaps I could send the spreadsheet and just change the client names?

 

[HAYLO22]

I think this gets what you want:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Client Name	Card No.	Medical Condition	Referral Date	Instructor	Status	Initial Appointment	Last Appointment	Next Appointment	Date Removed	Chase Up	Notes	Sessions Completed	Notes Completed	Expiry Date	Date Checked	INCOME FOR NOTES COMPLETED	Testimonials
3	Joe Bloggs			05/07/2022	Leigh	Completed	2022-08-18	2023-03-01					23	23	01/03/2023	01/03/2023	£598.00
4	Jill Bloggs			04/10/2022	Leigh	Completed	2022-10-22	2023-03-01					14	14	26/04/2023	12/04/2023	£364.00
5
6	HOP
7	INSTRUCTOR	TOTAL REMOVED CLIENTS	TOTAL COMPLETED CLIENTS	TOTAL REFERRED	TOTAL NOTES	sessions completed
8	Leigh	1	2	0		37
9
10	B104	C104
11	2023-03-01	2023-03-31
HAYLO22

Cell Formulas
Range	Formula
C8	C8	=COUNTIFS($E$3:$E$4,$A8,$F$3:$F$4,"Completed",$H$3:$H$4,">=" & $A$11,$H$3:$H$4,"<=" & $B$11)
F8	F8	=SUMIFS($M$3:$M$4,$E$3:$E$4,$A8,$F$3:$F$4,"Completed",$H$3:$H$4,">=" & $A$11,$H$3:$H$4,"<=" & $B$11)

Can I send you my spreadsheet please?
I have altered it so names removed etc so will be fine for you to view and you will get a better understanding? with different pages etc

 

[awoohaw]

can you post the sanitized workbook to a share site?

what is wrong with what I have above? All you need to to is paste the formula in your worksheet, then change the cell references by navigating and pointing to your specific locations. I suggest copying my formula with a ' (apostrophe) in front of it so you have a text version as a reference.

 

[awoohaw]

In the SUMIFS formula, I used hte number of completed sessions, not a number of "notes".

 

[HAYLO22]

can you post the sanitized workbook to a share site?

what is wrong with what I have above? All you need to to is paste the formula in your worksheet, then change the cell references by navigating and pointing to your specific locations. I suggest copying my formula with a ' (apostrophe) in front of it so you have a text version as a reference.

can you post the sanitized workbook to a share site?

what is wrong with what I have above? All you need to to is paste the formula in your worksheet, then change the cell references by navigating and pointing to your specific locations. I suggest copying my formula with a ' (apostrophe) in front of it so you have a text version as a reference.

The problem is me, I don't fully understand the formula and can't work it out for my spreadhseet.
How do I go about sharing my spreadsheet with you please?

 

[awoohaw]

just search for free file sharing platforms. dropbox.com is one.

 

[HAYLO22]

just search for free file sharing platforms. dropbox.com is one.

Zoho Accounts

Hope this works. Thank you
When you get onto it - it's the March 2023 I am interested in filling the Notes completed for completed clients sections. Even if you can just do one for one instructor I can do the rest.

Thank you so much.
It is a little confusing as the March info is based on the client completing the scheme in March however the notes could have been at any point. I am after the info for those that completed that month though. I hope that makes sense.

So for example if you did HOP completion for LEigh in MArch 2023 the answer should be 37 I just cant get the formula to work that out for me.

 

[awoohaw]

Haylo,
I've downloaded the workbook. The Notes column in HOP sheet is blank. What sheet are you getting the 37 for Leigh from?