sum distinct values given variable criteria

Thanks for helping a new member.

What I want to do is to sum the "current annual rev hrs" on the "RoleModules" worksheet for the roles entered under "Roles Assigned" on the "Totals" worksheet. I only included two roles to demonstrate the problem; however, there may be many dozens of role combinations that could be enterred.

Because the same module may exist in multiple roles, I want to account for these module separately. Specifically, I want the sums of the "current annual rev hrs" for modules that are common to the roles assigned to show up in Totals cells B2 and C2, and I want the sums for the modules that are unique to a given role to show up in columns D and E for each role assigned.

Thanks - but I do not see how that helps me differentiate between the modules that are unique to role1 (not also in role2).

OK, so I have given this some more thought. Suppose I have this data:

ROLE MODULE TIME
Role1 Mod1 1hr
Role1 Mod2 1hr
Role2 Mod1 1hr
Role2 Mod3 1hr

What I need to do is to calculate the total time for a person based on the roles assigned to that person. So, if a person is assigned role1 and role2, their total time would be 3 hrs, because they only need to do mod1 once. When I worked this manually to calculate the time by person, I filtered a list that included all roles and all modules assigned to each role by the role(s) assigned to that person, sorted the list by module, and then used the remove duplicates feature to remove any duplicate modules. The problem was that a new table had to be created for each combination of roles that each person might be assigned.

So I guess what I am asking is whether there is some way use a formula replicate the function of the remove duplicates feature without having to generate new tables for each combination of roles?

Sorry for that. I added the expected results in cell M4 and simplified the worksheet.

I'm sorry, I must be really slow today, but I don't see how this gives me the total for someone who has both both role1 and role2 without double counting the modules that are in both role1 and role2. I get that those formulas will give me the totals for role1 and role2, if both those roles were not assigned to the same person:

Sum HOURS Role 1 = 19.98
Sum HOURS Role 2 = 25.58

Combined this is 45.57.

But what I need it to do is to subtract out/not count the 12.15 HOURS that are in both role1 and role2, which gives me the "Sum HOURS Assigned Role 1 and 2 = 33.42"

On a separate sheet, I removed duplicates and summed the total. Was just hoping I could get the answer without having to do that for different role combinations.

Some modules are in both role1 and role2. I only want to count the hours of those modules one time. The 33.42 hours is the result of only summing the hours for each unique module one time. To perform that calculation manually using the attached spreadsheet, I use the remove duplicates feature to delete those rows where the same module is in both role1 and role2 and then sum the hours.

I'm very sorry for the late reply, but I have been out for the last couple of days. THANK YOU, that does it for me, thank you so much for your help. I truly appreciate it.