SUM Rows in Column A that meet multiple criteria including distinct values in Column B

DLFranklin85

I am trying to use a formula to:

Referencing other sheet(s)…

Sum story points by PE (Person) if:

1. The Parent PCR (Identifier) is Unique
2. The PCR Status is one of 5 options
3. The PE assigned is an exact match (ie "Derrick Franklin")

Here are a few that I feel are close, but not giving me the exact number's I'm looking for:

This one Counts all Story points in the full column for all PCRs (Not unique):

=SUMIF({Procedure/Process Change Request Unique Parent PCR}, COUNT(DISTINCT(COLLECT({Procedure/Process Change Request Unique Parent PCR}, {Procedure/Process Change PE}, "Derrick Franklin", {Procedure/Process Change PCR Status}, OR(@cell = "5.1 PE Planning", @cell = "5.2 PE Discovery", @cell = "5.3 PE Design", @cell = "5.4 PE Peer Review", @cell = "5.5 PE FA Review")))) >= 1, {Procedure/Process Change Story Points})

This one Counts All Story Points for "Derrick Franklin" but does not limit to Unique Parent PCRs:

=SUMIFS({Procedure/Process Change Story Points}, {Procedure/Process Change Request Unique Parent PCR}, COUNT(DISTINCT(COLLECT({Procedure/Process Change Request Unique Parent PCR}, {Procedure/Process Change PE}, "Derrick Franklin", {Procedure/Process Change PCR Status}, OR(@cell = "5.1 PE Planning", @cell = "5.2 PE Discovery", @cell = "5.3 PE Design", @cell = "5.4 PE Peer Review", @cell = "5.5 PE FA Review")))) <= 1, {Procedure/Process Change PE}, "Derrick Franklin", {Procedure/Process Change PCR Status}, OR(@cell = "5.1 PE Planning", @cell = "5.2 PE Discovery", @cell = "5.3 PE Design", @cell = "5.4 PE Peer Review"))

The goal for the below screenshots would return a value of 55 (5 Unique Parent PCR# with the specified statuses; 8+13+13+13+8)

Report Summary:

Worksheet Source for Formula (Very long workbook I don't directly maintain, so just select snips below):

Can anyone help me adjust this so it follows all of the 3 original criteria?

Lucas Rayala

can you put any helper column in the source sheet?

Also, how are you handling the fact you can have a parent PCR withtwo different story point values?