Sumproduct equation using a dropdown list for filtering

S

sbellmore

New Member
Joined
Oct 22, 2009
Messages
3
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)


100
101
102
project A
Project B
Project C

<tbody>
</tbody>

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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
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:

Issue TypeBug
Sprint100101102103104105
Project A5
04040
Project B
8165460
Project C000000

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>

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:

ProjectIssue TypeStatusSprintStory Points
Project ABugClosed.Net Sprint 95, Moodle UAP - Iteration 1002
Project AStoryClosed.Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 105, Moodle UAP - Iteration 1064
Project AStoryIn ProgressIteration 59, Moodle UAP - Iteration 1212
Project AStoryFutureIteration 68, Iteration 69, Iteration 70, Iteration 71, Moodle 3/30-4/13 -Iteration 79, Moodle 4/13-4/27 -Iteration 808
Project AWork OrderWork CompleteMobile 17.26 12/20-1/2, Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 1172
Project AStoryClosedMoodle 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 Sprint4
Project ABugIn ProductionMoodle 2/15- Iteration 1001
Project ABugIn ProductionMoodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 1001
Project ABugIn ProductionMoodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 1001
Project AStoryClosedMoodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 1024
Project AStoryClosedMoodle 2/15- Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 1094
Project AStoryClosedMoodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 1028
Project AStoryClosedMoodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 107, Moodle UAP - Iteration 1084
Project AStoryClosedMoodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 103, Moodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 1064
Project ABugClosedMoodle UAP - Iteration 1024
Project ABugClosedMoodle 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 1094
Project ABugClosedMoodle UAP - Iteration 1044
Project ABugReady for QA ReleaseMoodle UAP - Iteration 104, Moodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 1074
Project AStoryClosedMoodle UAP - Iteration 1054
Project AStoryClosedMoodle UAP - Iteration 1052
Project AStoryClosedMoodle UAP - Iteration 105, Moodle UAP - Iteration 106, Moodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 10916
Project ABugClosedMoodle 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 1138
Project AStoryClosedMoodle UAP - Iteration 107, Moodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 110, Moodle UAP - Iteration 1112
Project AStoryClosedMoodle UAP - Iteration 111, Moodle UAP - Iteration 1124
Project ABugClosedMoodle UAP - Iteration 112, Moodle UAP - Iteration 113, Moodle UAP - Iteration 114, Moodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 11716
Project AStoryClosedMoodle UAP - Iteration 113, Moodle UAP - Iteration 1148
Project AStoryQA TestingMoodle UAP - Iteration 115, Moodle UAP - Iteration 1168
Project ABugClosedMoodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 117
Project ABugClosedMoodle UAP - Iteration 115, Moodle UAP - Iteration 116, Moodle UAP - Iteration 1178
Project AWork OrderWork CompleteMoodle UAP - Iteration 116
Project AWork OrderWork CompleteMoodle UAP - Iteration 1212
Project BBugClosedMoodle UAP - Iteration 1024
Project BBugClosedMoodle UAP - Iteration 1042
Project BBugIn ProductionMoodle 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 1102
Project BBugClosedMoodle UAP - Iteration 101, Moodle UAP - Iteration 102, Moodle UAP - Iteration 1034
Project BBugClosedMoodle UAP - Iteration 1044
Project BBugFutureMoodle UAP - Iteration 100, Moodle UAP - Iteration 101, Moodle UAP - Iteration 1021
Project BBugClosed.Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 1018
Project BStoryClosedMoodle 7/20-8/3 - Iteration 87
Project BBugClosedMoodle 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 1008
Project BBugClosedMoodle 2/15- Iteration 100, .Net Sprint 95, Moodle UAP - Iteration 100, Moodle UAP - Iteration 1018
Project BStoryClosedMoodle 12/21- Iteration 968
Project BStoryClosedMoodle 6/22-7/6 - Iteration 85, Moodle 7/6-7/20 - Iteration 86, Moodle 7/20-8/3 - Iteration 878
Project CStoryClosedMoodle UAP - Iteration 117, Moodle UAP - Iteration 118, Moodle UAP - Iteration 119, Moodle UAP - Iteration 120, Recert 13 2/19/18 3/5/184
Project CBugReady for Staging ReleaseMoodle UAP - Iteration 112, Moodle UAP - Iteration 113, Moodle UAP - Iteration 114, Moodle UAP - Iteration 1158
Project CBugClosedMoodle UAP - Iteration 109, Moodle UAP - Iteration 1104
Project CStoryClosedMoodle UAP - Iteration 1098
Project CBugClosedMoodle UAP - Iteration 108, Moodle UAP - Iteration 109, Moodle UAP - Iteration 1102
Project CBugClosedMoodle UAP - Iteration 108, Moodle UAP - Iteration 1092
Project CBugClosedMoodle UAP - Iteration 108, Moodle UAP - Iteration 1092
Project CStoryClosedMoodle UAP - Iteration 1034
Project CWork OrderClosedMoodle UAP - Iteration 100, Moodle UAP - Iteration 1012
Project CWork OrderClosedMoodle UAP - Iteration 101, Moodle UAP - Iteration 1028
Project CStoryClosedMoodle UAP - Iteration 100, Moodle UAP - Iteration 1018

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I'm looking for any help on this... even telling me this isn't the right way to go about this.
 
Upvote 0
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".
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
writable cells limited by dropdown
Replies
1
Views
357
CharlesChuckieCharles
C
B
  • Solved
Dynamic dropdown list based on filtered table
Replies
4
Views
524
Georgiboy
Georgiboy
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
421
MissDB101
M
G
  • Question
I'd like to use a checkbox/active x control to drive a FILTER function, versus a dropdown
Replies
0
Views
374
Guitarmageddon
G
P
  • Solved
Add a dependent dropdown on UserForm with list constantly changing
Replies
6
Views
1K
frabulator
frabulator

Forum statistics

Threads
1,214,545
Messages
6,120,128
Members
448,947
Latest member
test111

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top