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]

Would you be kind enought to post sample data and expected outcomes using the xl2bb add in? (Link Below).
If you cannot use the add in, then please post the data and expectations as a table.

 

[HAYLO22]

Would you be kind enought to post sample data and expected outcomes using the xl2bb add in? (Link Below).
If you cannot use the add in, then please post the data and expectations as a table.

Hi, does this help at all?
I am not so great at this.
So far I have formulas which tell me how many of 'Leigh's' clients have completed during March 2023 but I would like to have a formula that tells me all of that and the total number sessions were completed by the clients that completed in March 2023.

 

[awoohaw]

Hi, does this help at all?
I am not so great at this.
So far I have formulas which tell me how many of 'Leigh's' clients have completed during March 2023 but I would like to have a formula that tells me all of that and the total number sessions were completed by the clients that completed in March 2023.

it is an image, which also means the scenario must be recreated which can lead to errors, and takes time.
Did you download and activate the xl2bb add in? Worst case, post the values and expectations in a table (just highlight range, copy, and paste - but with this you will not copy the formulas)

 

[awoohaw]

what is the value in Summary!B104? Is it a date?

 

[HAYLO22]

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

Joe Bloggs

05/07/2022

Leigh

Completed

18/08/2022

01/03/2023

23

23

01/03/2023

01/03/2023

£598.00

Jill Bloggs

04/10/2022

Leigh

Completed

22/10/2022

30/03/2023

14

14

26/04/2023

12/04/2023

£364.00

it is an image, which also means the scenario must be recreated which can lead to errors, and takes time. Did you download and activate the xl2bb add in? Worst case, post the values and expectations in a table (just highlight range, copy, and paste - but with this you will not copy the formulas)

HOP
INSTRUCTOR	TOTAL REMOVED CLIENTS	TOTAL COMPLETED CLIENTS	TOTAL REFERRED	TOTAL NOTES
Leigh	1​	2​	0​

I'm not sure I am doing this right. Unfortunately I can't download anything as it is a work computer.

 

[HAYLO22]

what is the value in Summary!B104? Is it a date?

Yes that is a date.

B104 01/03/2023​

C104 - 31/03/2023​

 

[HAYLO22]

Yes that is a date.

B104 01/03/2023​

C104 - 31/03/2023​

Let me know if you need anymore info. thank you

 

[awoohaw]

Okay, I did not make 3 worksheets, I hope you can figure out where you need tomake adjustments: Also, I use different date formats so you may need to fiddle with that some.

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
8	Leigh	1	2	0
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)

 

[awoohaw]

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....