SUM Rows in Column A that meet multiple criteria including distinct values in Column B
I am trying to use a formula to:
Referencing other sheet(s)…
Sum story points by PE (Person) if:
- The Parent PCR (Identifier) is Unique
- The PCR Status is one of 5 options
- 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?
Answers
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!