Assistance with Formula for Faculty Course Counting

Hello,

I’m working on a formula and could use some guidance. I have a list of faculty members who each teach multiple courses throughout the semester. My goal is to count each faculty member only once while summing the total value of the courses they teach—but ensuring that each course's value is counted only once per faculty member.

As of now this is the formula I have.

=COUNT(DISTINCT(COLLECT([Course Number]:[Course Number], [Instructor Name]:[Instructor Name], [Instructor Name]@row)))

This formula only counts how many courses they taught. But I need the sum of the courses value.

For example: Course 123 has a value of 5, Course 456 has a value of 6

John Doe:

Course 123 (5)

Course 456 (6)

In total they would have a sum of 11

Would anyone be able to help me determine the best way to structure this formula?

Thank you!

Find more posts tagged with

Accepted answers

All comments

jlyon1

Hard to tell exactly how you have the data set up without a screenshot, but here are 2 different versions for both potential structures. (unless I'm picturing something totally different from what you're asking)

Count all non-null course codes for a given instructor name:

=IFERROR(IF([Instructor Name]:[Instructor Name] = [Instructor Name]@row, COUNT([Course Number]:[Course Number], 0),"")

Return 1 for rows with a distinct course+instructor combo listed or 0 for non-unique courses, then sum that column using a sheet summary or total row:

=IF(COUNTIF([Course Number]:[Course Number], [Course Number]@row) <2, 1, 0) + IF(COUNTIF([Instructor Name]:[Instructor Name], [Instructor Name]@row) <2, 1, 0)

alegv

Hello!

Thank you!! perhaps this screenshot can help! please see below.

Screenshot 2025-02-19 at 11.36.12 AM.png

As you can see Ivonne teaches course 3331.S which is an equivalent to Workload total of 1 as well as Arlene who teaches 3530.S that is worth 5. I need to come up with a formula where the formula conducts the following:

Ivonne who teaches 3331.S workload is 3.

Arlene who teaches 3530.S workload is 10.

I hope this is a better explanation 😪 I could really use the help!

Thank you!!!

Erin Horiuchi Green

Hello @alegv ,

There are other ways to approach this. Without a more indepth discovery call with you, then this is just off the top of my head.

Recommendation #1 - Add Join Columns & Report

1) Create Join Column of Instructor Name & Course Number
=JOIN([Instructor Name]@row + " " + [Course Number]@row)

2) Create a Row Report. Abilit to filter out BLANK records. Easily pull in as a Dashboard Chart.
Fastest refresh response time (5 - 15 min) compared to Pivot.

Recommendation #2 - Add Join Columns & Pivot

1) Create Join Column of Instructor Name & Course Number
=JOIN([Instructor Name]@row + " " + [Course Number]@row)

2) Create a Pivot. No option to remove BLANK records.
Refresh response time anywhere between 5 min - 1 hour. If refresh response time > 1 hour, contact Support.

alegv

@Erin Horiuchi Green

Hi Erin!

Thank you so much for taking the time to do this for me!

Unfortunately, they want to see the instructor and workload together. The screenshot that I posted was just a snip it because in reality there are 30+ courses. So in the report would look a little crazy :(

Erin Horiuchi Green

Hello @alegv ,

In that case, then here are 2 additional recommendations.

You have to manually drop down the formula because the reference arrays expand as the records grow.

There is no automation available to dynamically drop formula as new records are added either.

Recommendation #3 - From Row 1, Only Aggregate by 1st Unique Record
=IF(COUNTIF([Instructor Name + Course #]$1:[Instructor Name + Course #]1, [Instructor Name + Course #]@row) > 1, 0, [Instructor Name + Course #]@row + " (" + SUMIF([Instructor Name + Course #]:[Instructor Name + Course #], [Instructor Name + Course #]@row, [Workload Total]:[Workload Total]) + ")")

Recommendation #4 - From Row 1, Aggregate from 1st Unique Record & Forward
=[Instructor Name + Course #]@row + " (" + SUMIF([Instructor Name + Course #]$1:[Instructor Name + Course #]1, [Instructor Name + Course #]@row, [Workload Total]$1:[Workload Total]1) + ")"

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.