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.
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.
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 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | H | I | J | |||||
| 2 | Agent | Date | Module | Hours | Pass Y/N | Facilitator | Facilitator Hours | ||||
| 3 | Cantrell, Charles | 3/9/22 | VoIP | 2 | N | Fresh Prince | 3 | ||||
| 4 | Christensen, Nicholas | 3/9/22 | VoIP | 2 | N | Fresh Prince | 3 | ||||
| 5 | Crowley, Evan | 3/10/22 | VoIP | 0 | N | Fresh Prince | 3 | ||||
| 6 | Baird, David | 3/10/22 | IqniteNet | 2 | Y | Doctor Dre | 3 | ||||
| 7 | Baker, Joseph | 3/10/22 | IqniteNet | 2 | Y | Doctor Dre | 3 | ||||
| 8 | Bowes, Alexandra | 3/10/22 | IqniteNet | 2 | Y | Doctor Dre | 3 | ||||
| 9 | Brakke, Bryson | 3/10/22 | VoIP | 2 | Y | Fresh Prince | 3 | ||||
| 10 | Briceno, Austin L | 3/10/22 | Adv Tools | 2 | N | Fresh Prince | 3 | ||||
| 11 | Cano, Arthur | 3/10/22 | Adv Tools | 2 | Y | Fresh Prince | 3 | ||||
| 12 | Cantrell, Charles | 3/10/22 | Adv Tools | 2 | Y | Fresh Prince | 3 | ||||
| 13 | Casanova, Erica V | 3/10/22 | Upgrades | 2 | Y | Doctor Dre | 3 | ||||
| 14 | Chesnick, Kyle | 3/10/22 | Upgrades | 2 | N | Doctor Dre | 3 | ||||
| 15 | Christensen, Nicholas | 3/10/22 | Upgrades | 2 | N | Doctor Dre | 3 | ||||
| 16 | Crowley, Evan | 3/10/22 | Trango/Tran | 2 | N | Eminem | 3 | ||||
| 17 | Davila, Mercedes | 3/10/22 | Trango/Tran | 2 | Y | Eminem | 3 | ||||
| 18 | Delon, Anthony | 3/10/22 | Trango/Tran | 2 | Y | Eminem | 3 | ||||
| 19 | Derringer, Ryan | 3/10/22 | Trango/Tran | 2 | Y | Eminem | 3 | ||||
| 20 | DuBard, Chase | 3/10/22 | Billing | 2 | Y | Fresh Prince | 3 | ||||
| 21 | Dunn, Cody | 3/10/22 | Billing | 2 | Y | Fresh Prince | 3 | ||||
| 22 | Erickson, Jason | 3/10/22 | Billing | 2 | N | Fresh Prince | 3 | ||||
Training Log | |||||||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D3:D22 | List | =Formulas!$B$4:$B$13 |
| B3:B22 | List | ='Learner Roll-Up'!$B$3:$B$56 |
| I3:I22 | List | =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.
