Sumproduct equation using a dropdown list for filtering

[sbellmore]

Hi, I'm creating a sheet that shows story points per sprint in order to chart it. The table has projects in the first row and sprints for the columns and the data is filtered based on these and dropdown cells. Is there a way to put a wildcard into the dropdown cell that will allow the data to count against all or any option in that particular field? I'll try to include an example:

dropdown1 contains (Item Type): Bug, Story, Work Order, ALL (this is what is not working)


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]102
[/TD]
[/TR]
[TR]
[TD]project A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

my equation: =sumproduct( storypoints, --(project=$a$2),--(sprint=B1),--(item type=dropdownlistpick))
so it basically adds up the story points from a large data set where projects and sprints match what's in a table but there can be other search criteria in dropdowns. if the user wants to have those dropdowns capture any/all values for that field would that be added in the dropdown list or as something in the equation?

this is for Excel2016 btw and i'm not actually using the word All.. but I don't know if there is some wildcard I can add to the dropdown that would capture all.

 

[sbellmore]

Ok, maybe a slightly better example could help. I've got 2 worksheets in a book: Dashboard and general_report. Dashboard has the calculated sums while general_report has the data.

Here's what's in Dashboard:

[TABLE="width: 747"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Issue Type[/TD]
[TD]Bug
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sprint[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD]Project B
[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Issue Type in B2 is a dropdown list that contains (Bug, Story, Work Order, Wildcard) Wildcard would be the magic something i could add that would get me the summed value for all of the entries in that array. I'm not sure if this is done in the dropdown list or in the equation itself

The equation that provides the green values is as follows (just giving B4 cell):
=SUMPRODUCT(--(general_report!$E2:$E56),--(general_report!$A2:$A56=Dashboard!$A4),--(general_report!$B2:$B56=Dashboard!$B$1),--(RIGHT(general_report!$D2:$D56,3)=TEXT(Dashboard!B$3,"###")))

general_report worksheet contains data like this:

[TABLE="width: 896"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Issue Type[/TD]
[TD]Status[/TD]
[TD]Sprint[/TD]
[TD]Story Points[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD].Net Sprint 95, Moodle UAP - Iteration 100[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD].Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]In Progress[/TD]
[TD]Iteration 59, Moodle UAP - Iteration 121[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Future[/TD]
[TD]Iteration 68, Iteration 69, Iteration 70, Iteration 71, Moodle 3/30-4/13 -Iteration 79, Moodle 4/13-4/27 -Iteration 80[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Work Order[/TD]
[TD]Work Complete[/TD]
[TD]Mobile 17.26 12/20-1/2, Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 117[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 10/26- Iteration 93, Moodle 11/9- Iteration 94, Moodle 12/7- Iteration 95, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110, Moodle UAP - Iteration 111, Moodle UAP - Iteration 112, OLD CSL Sprint[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]In Production[/TD]
[TD]Moodle 2/15- Iteration 100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]In Production[/TD]
[TD]Moodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]In Production[/TD]
[TD]Moodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 2/15- Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 103, Moodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 102[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 103, Moodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 104[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Ready for QA Release[/TD]
[TD]Moodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 105[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 105[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110, Moodle UAP - Iteration 111, Moodle UAP - Iteration 112, Moodle UAP - Iteration 113[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110, Moodle UAP - Iteration 111[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 111, Moodle UAP - Iteration 112[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 112, Moodle UAP - Iteration 113, Moodle UAP - Iteration 114, Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 117[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 113, Moodle UAP - Iteration 114[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Story[/TD]
[TD]QA Testing[/TD]
[TD]Moodle UAP - Iteration 115, Moodle UAP - Iteration 116[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 117[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 117[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Work Order[/TD]
[TD]Work Complete[/TD]
[TD]Moodle UAP - Iteration 116[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]Work Order[/TD]
[TD]Work Complete[/TD]
[TD]Moodle UAP - Iteration 121[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 102[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 104[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]In Production[/TD]
[TD]Moodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 103[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 104[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Future[/TD]
[TD]Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD].Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 7/20-8/3 - Iteration 87[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle 1/4- Iteration 97, Moodle 1/18- Iteration 98, Moodle 2/1- Iteration 99, Moodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 12/21- Iteration 96[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle 6/22-7/6 - Iteration 85, Moodle 7/6-7/20 - Iteration 86, Moodle 7/20-8/3 - Iteration 87[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 117, Moodle UAP - Iteration 118, Moodle UAP - Iteration 119, Moodle UAP - Iteration 120, Recert 13 2/19/18 3/5/18[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Bug[/TD]
[TD]Ready for Staging Release[/TD]
[TD]Moodle UAP - Iteration 112, Moodle UAP - Iteration 113, Moodle UAP - Iteration 114, Moodle UAP - Iteration 115[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 109, Moodle UAP - Iteration 110[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 109[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 108, Moodle UAP - Iteration 109[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Bug[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 108, Moodle UAP - Iteration 109[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 103[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Work Order[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 100, Moodle UAP - Iteration 101[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Work Order[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 101, Moodle UAP - Iteration 102[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]Story[/TD]
[TD]Closed[/TD]
[TD]Moodle UAP - Iteration 100, Moodle UAP - Iteration 101[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for any help on this... even telling me this isn't the right way to go about this.

 

[RoryA]

Assuming Item type would never actually be "All", you could use something like this:

=sumproduct( storypoints, (project=$a$2)*(sprint=B1)*((item type=dropdownlistpick)+(dropdownlistpick="All")))

so that the part in between the blue brackets will return 1 if either the item type matches the dropdown or if the dropdown contains "All".

 

[sbellmore]

Thanks, that works! Very much appreciated.