Using functions in a sheet summary

j.hung

I'm designing a tracker to track training credits. These training credits are used by specific learners who belong to specific workstreams. I'm trying to use the sheet summary to answer these 2 questions:

• How many training credits are used by a specific workstream, e.g., Technical?
• How many training credits are used by a specific learner, e.g., Jennifer?

So far, I have set out the tracker and the sheet summary as thus:

I was thinking that, in the sheet summary, the "Total Training Credits (By Workstream)" and "Total Training Credits (By Learner)" fields would be functions that are based on the "Workstream" and "Learner" fields (in addition to the "Training Credit" column in the tracker), such that:

• If I have the workstream as Technical and the learner as Jennifer, I would get 9.5 total training credits by workstream and 8 total training credits by learner, or that
• If I have the workstream to Functional and the learner as Eddie, I would get 4 total training credits by workstream and 4 total training credits by learner.

This is a work-in-progress, so I'm open to suggestions if there are better ways to answer the questions listed above.

Thank you!

brianschmidt

For the Learner summary field, I would try this:

=SUM(COLLECT([Training Credit]:[Training Credit], Learner:Learner, Learner#)

For the Workstream summary field, I would try this:

=SUM(COLLECT([Training Credit]:[Training Credit], Workstream:Workstream, Workstream#)

Hope this helps!:)