Formula Help Please

H

HAYLO22

New Member
Joined
Jul 8, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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.
INSTRUCTORTOTAL REMOVED CLIENTSTOTAL COMPLETED CLIENTSTOTAL 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.
 
Sort by date Sort by votes

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Okay, I have the Notes Completed column calculating correctly. One thing I did was change the "Leigh" to the cell reference... and darn... it did not work.
But then I looked at the names column. Leigh was spelled "Leigh "... which means the match fails. Once corrected the formula calculates to 37.
That said. You are using table to put tables together which I guess is fine, but with this large amount of data I think it could be easy to get corrupted.
If you don't know Power Pivot, or Power Query I suggest you look into them for this project. i am not an expert in either of those and am content with doing worksheets much in the same way you have until i become more proficient in PQ and PP. Here is the section I think that was troubling you:



Referral Info HC .xlsx
GHIJK
24HOP
25INSTRUCTORTOTAL REMOVED CLIENTS TOTAL COMPLETED CLIENTS TOTAL REFERREDTOTAL NOTES for completed clients
26Annette1010
27Ben0000
28Ellie0000
29John0000
30Jonny0000
31Kyle0000
32Leigh12037
33Linden0000
34Luke0000
35Nick0000
36Nicola11028
37Steve0000
38Tom0000
39Rob1010
40Benjamin0000
41Sizwe0000
42Jay1000
43TOTALS532
44
MARCH 2023
Cell Formulas
RangeFormula
H26:H42H26=COUNTIFS(HOP!$E$2:$E$5000,[@INSTRUCTOR],HOP!$F$2:$F$5000,"Removed",HOP!$J$2:$J$5000,">=" & Summary!$B$104,HOP!$J$2:$J$5000,"<=" & Summary!$C$104)
I26:I42I26=COUNTIFS(HOP!$E$2:$E$5000,[@INSTRUCTOR],HOP!$F$2:$F$5000,"Completed",HOP!$H$2:$H$5000,">=" & Summary!$B$104,HOP!$H$2:$H$5000,"<=" & Summary!$C$104)
J26:J42J26=COUNTIFS(HOP!$E$2:$E$5000,[@INSTRUCTOR],HOP!$D$2:$D$5000,">=" & Summary!$B$104,HOP!$D$2:$D$5000,"<=" & Summary!$C$104)
K26:K42K26=SUMIFS(HOP!$N$2:$N$5000,HOP!$E$2:$E$5000,[@INSTRUCTOR],HOP!$F$2:$F$5000,"Completed",HOP!$H$2:$H$5000,">=" & Summary!$B$104,HOP!$H$2:$H$5000,"<=" & Summary!$C$104)
H43:J43H43=SUM(H26:H42)
 
Upvote 0
awoohaw said:
Haylo,
I've downloaded the workbook. The Notes column in HOP sheet is blank. What sheet are you getting the 37 for Leigh from?
Click to expand...
They are blank because I can’t work it out. It should say 37 with the correct formula based on the HOP sheet.

Leigh has two clients complete in March 2023 which is great info but I need to know the combined number of notes/sessions for those clients. Does that make sense l?
 
Upvote 0
awoohaw said:
Okay, I have the Notes Completed column calculating correctly. One thing I did was change the "Leigh" to the cell reference... and darn... it did not work.
But then I looked at the names column. Leigh was spelled "Leigh "... which means the match fails. Once corrected the formula calculates to 37.
That said. You are using table to put tables together which I guess is fine, but with this large amount of data I think it could be easy to get corrupted.
If you don't know Power Pivot, or Power Query I suggest you look into them for this project. i am not an expert in either of those and am content with doing worksheets much in the same way you have until i become more proficient in PQ and PP. Here is the section I think that was troubling you:
Referral Info HC .xlsx
FGHIJK
23
24HOP
25INSTRUCTORTOTAL REMOVED CLIENTS TOTAL COMPLETED CLIENTS TOTAL REFERREDTOTAL NOTES for completed clients
26Annette1010
27Ben0000
28Ellie0000
29John0000
30Jonny0000
31Kyle0000
32Leigh12037
33Linden0000
34Luke0000
35Nick0000
36Nicola11028
37Steve0000
38Tom0000
39Rob1010
40Benjamin0000
41Sizwe0000
42Jay1000
43TOTALS532
44
MARCH 2023
Cell Formulas
RangeFormula
H26H26=COUNTIFS(HOP!E2:E5000,"Annette",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I26I26=COUNTIFS(HOP!E2:E5000,"Annette",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J26J26=COUNTIFS(HOP!E2:E5000,"Annette",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
K26:K42K26=SUMIFS(HOP!$N$2:$N$5000,HOP!$E$2:$E$5000,[@INSTRUCTOR],HOP!$F$2:$F$5000,"Completed",HOP!$H$2:$H$5000,">=" & Summary!$B$104,HOP!$H$2:$H$5000,"<=" & Summary!$C$104)
H27H27=COUNTIFS(HOP!E2:E5000,"Ben",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I27I27=COUNTIFS(HOP!E2:E5000,"Ben",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J27J27=COUNTIFS(HOP!E2:E5000,"Ben",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H28H28=COUNTIFS(HOP!E2:E5000,"Ellie",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I28I28=COUNTIFS(HOP!E2:E5000,"Ellie",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J28J28=COUNTIFS(HOP!E2:E5000,"Ellie",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H29H29=COUNTIFS(HOP!E2:E5000,"John",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I29I29=COUNTIFS(HOP!E2:E5000,"John",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J29J29=COUNTIFS(HOP!E2:E5000,"John",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H30H30=COUNTIFS(HOP!E2:E5000,"Jonny",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I30I30=COUNTIFS(HOP!E2:E5000,"Jonny",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J30J30=COUNTIFS(HOP!E2:E5000,"Jonny",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H31H31=COUNTIFS(HOP!E2:E5000,"Kyle",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I31I31=COUNTIFS(HOP!E2:E5000,"Kyle",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J31J31=COUNTIFS(HOP!E2:E5000,"Kyle",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H32H32=COUNTIFS(HOP!E2:E5000,"Leigh",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I32I32=COUNTIFS(HOP!E2:E5000,"Leigh",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J32J32=COUNTIFS(HOP!E2:E5000,"Leigh",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H33H33=COUNTIFS(HOP!E2:E5000,"Linden",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I33I33=COUNTIFS(HOP!E2:E5000,"Linden",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J33J33=COUNTIFS(HOP!E2:E5000,"Linden",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H34H34=COUNTIFS(HOP!E2:E5000,"Luke",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I34I34=COUNTIFS(HOP!E2:E5000,"Luke",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J34J34=COUNTIFS(HOP!E2:E5000,"Luke",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H35H35=COUNTIFS(HOP!E2:E5000,"Nick",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I35I35=COUNTIFS(HOP!E2:E5000,"Nick",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J35J35=COUNTIFS(HOP!E2:E5000,"Nick",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H36H36=COUNTIFS(HOP!E2:E5000,"Nicola",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I36I36=COUNTIFS(HOP!E2:E5000,"Nicola",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J36J36=COUNTIFS(HOP!E2:E5000,"Nicola",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H37H37=COUNTIFS(HOP!E2:E5000,"Steve",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I37I37=COUNTIFS(HOP!E2:E5000,"Steve",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J37J37=COUNTIFS(HOP!E2:E5000,"Steve",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H38H38=COUNTIFS(HOP!E2:E5000,"Tom",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I38I38=COUNTIFS(HOP!E2:E5000,"Tom",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J38J38=COUNTIFS(HOP!E2:E5000,"Tom",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H39H39=COUNTIFS(HOP!E2:E5000,"Rob",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I39I39=COUNTIFS(HOP!E2:E5000,"Rob",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J39J39=COUNTIFS(HOP!E2:E5000,"Rob",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H40H40=COUNTIFS(HOP!E2:E5000,"Benjamin",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I40I40=COUNTIFS(HOP!E2:E5000,"Benjamin",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J40J40=COUNTIFS(HOP!E2:E5000,"Benjamin",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H41H41=COUNTIFS(HOP!E2:E5000,"Sizwe",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I41I41=COUNTIFS(HOP!E2:E5000,"Sizwe",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J41J41=COUNTIFS(HOP!E2:E5000,"Sizwe",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H42H42=COUNTIFS(HOP!E2:E5000,"Jay",HOP!F2:F5000,"Removed",HOP!J2:J5000,">=" & Summary!$B$104,HOP!J2:J5000,"<=" & Summary!$C$104)
I42I42=COUNTIFS(HOP!E2:E5000,"Jay",HOP!F2:F5000,"Completed",HOP!H2:H5000,">=" & Summary!$B$104,HOP!H2:H5000,"<=" & Summary!$C$104)
J42J42=COUNTIFS(HOP!E2:E5000,"Jay",HOP!D2:D5000,">=" & Summary!$B$104,HOP!D2:D5000,"<=" & Summary!$C$104)
H43:J43H43=SUM(H26:H42)
HAYLO22 said:
They are blank because I can’t work it out. It should say 37 with the correct formula based on the HOP sheet. Leigh has two clients complete in March 2023 which is great info but I need to know the combined number of notes/sessions for those clients. Does that make sense l?
Click to expand...
thank you so much! That look perfect. You are right my file is corrupt and I couldn’t work out why! I’m pretty new to using excel so still a bit of a learning process for me. Thank you so much for your help.
Click to expand...
Cell Formulas
RangeFormula
 
Upvote 0
I just did an edit to post 22. you may need to refresh your screen. I fixed all the formulas in that section so they are consistent.
 
Upvote 0
the totals are not right, It may be because I copied the formulas down, and may have overwritten the totals row.
 
Upvote 0
awoohaw said:
the totals are not right, It may be because I copied the formulas down, and may have overwritten the totals row.
Click to expand...
That’s fine, I can look at that. Thank you so much. Such a huge help as I just couldn’t figure it out!
 
Upvote 0
Sometimes a second set of eyes is all that is needed. I'm happy you were able to find a solution from the forum.

If you think one of the posts provides you with the answer please mark it as the solution.

Best wishes!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
multiple cell len formula
Replies
9
Views
53
Fluff
Fluff
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
445
KRice
K
K
  • Question
Formula needed to look up specific name and enter data from specific column within same row
Replies
1
Views
193
awoohaw
awoohaw
H
  • Solved
Collecting data from a specific date range.
2
Replies
10
Views
520
HAYLO22
H
J
  • Solved
Excel formula help!!!
Replies
4
Views
286
felixstraube
F

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top