Calculating total classes using just date and module

K

kxellis26

New Member
Joined
Mar 11, 2022
Messages
31
Office Version
  1. 365
Platform
  1. Windows
I can't wait to hear how bad I overcomplicated the current formula I'm using, because I know I did.

I am trying to calculate the total number of classes completed by each facilitator using only the date and name of the module. Sometimes there are 2 people in class, sometimes there are 15. Without having a unique identifier, or tracking separately, I can't seem to find a good formula to use. The tab looks like this and has over 500 rows of data, with more to come.

Certification Tracker.xlsx
BCDEHIJ
2AgentDateModuleHoursPass Y/NFacilitatorFacilitator Hours
3Cantrell, Charles3/9/22VoIP2NFresh Prince3
4Christensen, Nicholas3/9/22VoIP2NFresh Prince3
5Crowley, Evan3/10/22VoIP0NFresh Prince3
6Baird, David3/10/22IqniteNet2YDoctor Dre3
7Baker, Joseph3/10/22IqniteNet2YDoctor Dre3
8Bowes, Alexandra3/10/22IqniteNet2YDoctor Dre3
9Brakke, Bryson3/10/22VoIP2YFresh Prince3
10Briceno, Austin L3/10/22Adv Tools2NFresh Prince3
11Cano, Arthur3/10/22Adv Tools2YFresh Prince3
12Cantrell, Charles3/10/22Adv Tools2YFresh Prince3
13Casanova, Erica V3/10/22Upgrades2YDoctor Dre3
14Chesnick, Kyle3/10/22Upgrades2NDoctor Dre3
15Christensen, Nicholas3/10/22Upgrades2NDoctor Dre3
16Crowley, Evan3/10/22Trango/Tran2NEminem3
17Davila, Mercedes3/10/22Trango/Tran2YEminem3
18Delon, Anthony3/10/22Trango/Tran2YEminem3
19Derringer, Ryan3/10/22Trango/Tran2YEminem3
20DuBard, Chase3/10/22Billing2YFresh Prince3
21Dunn, Cody3/10/22Billing2YFresh Prince3
22Erickson, Jason3/10/22Billing2NFresh Prince3
Training Log
Cells with Data Validation
CellAllowCriteria
D3:D22List=Formulas!$B$4:$B$13
B3:B22List='Learner Roll-Up'!$B$3:$B$56
I3:I22List=Formulas!$D$4:$D$11


The current formula I'm using is to pull the classes into another tab is...

=SUMPRODUCT(1*(FREQUENCY(IF('Training Log'!D:D=$C10,IF('Training Log'!I:I=$B$3,MATCH('Training Log'!C:C,'Training Log'!C:C,0))),ROW('Training Log'!C:C)-ROW(Table4[[#Headers],[Facilitator]]))>0))

I know it has to be easier than that. Any help is appreciated.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I haven't tried to recreate your formula ... but are these the results you're looking for?

ABCDEFGHIJKLM
1
2AgentDateModuleHoursPass Y/NFacilitatorFacilitator Hours
3Cantrell, Charles9 Mar 2022VoIP2NFresh Prince3Doctor Dre2
4Christensen, Nicholas9 Mar 2022VoIP2NFresh Prince3Eminem1
5Crowley, Evan10 Mar 2022VoIP0NFresh Prince3Fresh Prince4
6Baird, David10 Mar 2022IqniteNet2YDoctor Dre3
7Baker, Joseph10 Mar 2022IqniteNet2YDoctor Dre3
8Bowes, Alexandra10 Mar 2022IqniteNet2YDoctor Dre3
9Brakke, Bryson10 Mar 2022VoIP2YFresh Prince3
10Briceno, Austin L10 Mar 2022Adv Tools2NFresh Prince3
11Cano, Arthur10 Mar 2022Adv Tools2YFresh Prince3
12Cantrell, Charles10 Mar 2022Adv Tools2YFresh Prince3
13Casanova, Erica V10 Mar 2022Upgrades2YDoctor Dre3
14Chesnick, Kyle10 Mar 2022Upgrades2NDoctor Dre3
15Christensen, Nicholas10 Mar 2022Upgrades2NDoctor Dre3
16Crowley, Evan10 Mar 2022Trango/Tran2NEminem3
17Davila, Mercedes10 Mar 2022Trango/Tran2YEminem3
18Delon, Anthony10 Mar 2022Trango/Tran2YEminem3
19Derringer, Ryan10 Mar 2022Trango/Tran2YEminem3
20DuBard, Chase10 Mar 2022Billing2YFresh Prince3
21Dunn, Cody10 Mar 2022Billing2YFresh Prince3
22Erickson, Jason10 Mar 2022Billing2NFresh Prince3
23
Sheet1
Cell Formulas
RangeFormula
L3:L5L3=SORT(UNIQUE(I3:I22))
M3:M5M3=ROWS(UNIQUE(FILTER(C$3:D$22,I$3:I$22=L3)))
Dynamic array formulas.
 
Upvote 0
Yes, that's exactly what I'm looking for! I knew that pile of rubbish was hogwash. I just pieced a bunch of stuff together and spent wayyyy too much time. I have never taken any Excel classes, just kind of nerd out on it as much as I can.

Thank you so much!
 
Upvote 0
I was playing with it and realized that it does pull all of the trainings, but not by "module" here is a snapshot of what I'm trying to fill out with this data:

Certification Tracker - NEW.xlsx
BCDEF
32FacilitatorModuleModules TrainedModule HoursCertified Y/N
33EminemCambium00
34VoIP00
35Adv Tools00
36Billing00
37Upgrades00
38Trango/Tran00
39IqniteNet00
40CPE Zabbix00
Facilitator Roll-Up
Cell Formulas
RangeFormula
D33:D40D33=SUMPRODUCT(1*(FREQUENCY(IF('Training Log'!D:D=$C33,IF('Training Log'!I:I=$B$33,MATCH('Training Log'!C:C,'Training Log'!C:C,0))),ROW('Training Log'!C:C)-ROW(Table4[[#Headers],[Facilitator]]))>0))
E33:E40E33=D33*2


So I actually need to know how many times they have trained each module and how many hours they have trained to get them certified. I don't think I explained that well. However, what you posted is perfect if I want to know the total numbers of modules they have trained. In order to be certified, they have to train each module a certain amount of times. If that makes sense.
 
Upvote 0
Like this perhaps, although I'm not sure what numbers you want to appear in col Q?

ABCDEFGHIJKLMNOPQ
1
2AgentDateModuleHoursPass Y/NFacilitatorFacilitator HoursFresh PrinceNo timesNo hrs
3Cantrell, Charles9 Mar 2022VoIP2NFresh Prince3Doctor Dre2Adv Tools1
4Christensen, Nicholas9 Mar 2022VoIP2NFresh Prince3Eminem1Billing1
5Crowley, Evan10 Mar 2022VoIP0NFresh Prince3Fresh Prince4IqniteNet0
6Baird, David10 Mar 2022IqniteNet2YDoctor Dre3Trango/Tran0
7Baker, Joseph10 Mar 2022IqniteNet2YDoctor Dre3Upgrades0
8Bowes, Alexandra10 Mar 2022IqniteNet2YDoctor Dre3VoIP2
9Brakke, Bryson10 Mar 2022VoIP2YFresh Prince3
10Briceno, Austin L10 Mar 2022Adv Tools2NFresh Prince3
11Cano, Arthur10 Mar 2022Adv Tools2YFresh Prince3
12Cantrell, Charles10 Mar 2022Adv Tools2YFresh Prince3
13Casanova, Erica V10 Mar 2022Upgrades2YDoctor Dre3
14Chesnick, Kyle10 Mar 2022Upgrades2NDoctor Dre3
15Christensen, Nicholas10 Mar 2022Upgrades2NDoctor Dre3
16Crowley, Evan10 Mar 2022Trango/Tran2NEminem3
17Davila, Mercedes10 Mar 2022Trango/Tran2YEminem3
18Delon, Anthony10 Mar 2022Trango/Tran2YEminem3
19Derringer, Ryan10 Mar 2022Trango/Tran2YEminem3
20DuBard, Chase10 Mar 2022Billing2YFresh Prince3
21Dunn, Cody10 Mar 2022Billing2YFresh Prince3
22Erickson, Jason10 Mar 2022Billing2NFresh Prince3
23
Sheet1
Cell Formulas
RangeFormula
O2O2=L5
L3:L5L3=SORT(UNIQUE(I3:I22))
M3:M5M3=ROWS(UNIQUE(FILTER(C$3:D$22,I$3:I$22=L3)))
P3:P8P3=COUNT(UNIQUE(FILTER(C$3:C$22,(I$3:I$22=O$2)*(D$3:D$22=O3),"")))
Dynamic array formulas.
 
Upvote 0
Solution
StephenCrump said:
Great, I'm glad it worked for you.
Click to expand...
Hey, Stephen. I am really new at this and don't know if you saw my response. I don't think I explained it well enough, so I'm sorry about that. Really appreciate the help.
 
Upvote 0
This is EXACTLY what I was looking for. Dude, you are the man. Much appreciated for the Friday night response and all of your help. I didn't really need to change what I had, because it worked, but I would rather understand it. Enjoy your weekend!

Holler next time you are in Colorado and I will get you a fresh brew.
 
Upvote 0
Hey, Stephen. Not sure if I should open up another thread, but this one is getting more complicated. I now need to figure out how many courses each person has completed for each module, each month. Any thoughts?

I am thinking that I will calculate it in another tab, so it's not off to the side and the formulas can't be erased by someone entering data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
105
awoohaw
awoohaw
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
374
KRice
K
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
186
awoohaw
awoohaw
P
  • Question
Having trouble with forumla to use Countifs for only specific names listed in a column
Replies
1
Views
451
Paradigmgaming911
P
JoshLyman
  • Solved
VBA - Insert row before the Total row in my table
Replies
3
Views
362
Alex Blakenburg
A

Forum statistics

Threads
1,215,374
Messages
6,124,574
Members
449,173
Latest member
Kon123

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