# help with SUMPRODUCT or an alternative solution

#### J4L

##### New Member
Hi All,

I am hoping someone out there is able to assist me. I am trying to summarise huge amount of data and the pivot table option is not really feasble choice, due to end users preferations.

In the 'DATA' tab i have given specific columns a Name Range and the 'Summary' tab i am using SUMPRODUCT((Name Range = A1)*(Name Range = B2)) etc

which works fine, but i have multiple criteria's to incorporate in the formula and not quite sure whether the SUMPRODUCT is the best way forward.

since its been a while that I have posted on this forum, can somebody kindly remind me how I can upload a sample file/worksheet

Thanks
Jal.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Basically what i'm trying to achieve in the 'Summary' tab is show the Total Column E, against Column A, B & C

E.g.

Week-ending 2015-04-05, Indicator = N, Classification = DC and Main_Spec = Grade - A - this would give me the sum of 3

formula attached a further down.

DATA TAB

Excel 2007
ABCDEF
1Week_EndingINDICATORClassificationMain_SpecTotalPlan_Figure

<tbody>
</tbody>
DATA

Excel 2007
ABCDEFGH
2
32015-04-052015-04-122015-04-192015-04-26
4E2333
5DC0000
6N2222
7DC0000
8

</tbody>
Summary

Worksheet Formulas
CellFormula
D4=SUMPRODUCT((((we=D\$3)*(spec=\$B\$1)*(indicator=\$C\$4))))
E4=SUMPRODUCT((((we=E\$3)*(spec=\$B\$1)*(indicator=\$C\$4))))
F4=SUMPRODUCT((((we=F\$3)*(spec=\$B\$1)*(indicator=\$C\$4))))
G4=SUMPRODUCT((((we=G\$3)*(spec=\$B\$1)*(indicator=\$C\$4))))
D5=SUMPRODUCT((((we=D\$3)*(spec=\$B\$1)*(indicator=\$C\$5))))
E5=SUMPRODUCT((((we=E\$3)*(spec=\$B\$1)*(indicator=\$C\$5))))
F5=SUMPRODUCT((((we=F\$3)*(spec=\$B\$1)*(indicator=\$C\$5))))
G5=SUMPRODUCT((((we=G\$3)*(spec=\$B\$1)*(indicator=\$C\$5))))
D6=SUMPRODUCT((((we=D\$3)*(spec=\$B\$1)*(indicator=\$C\$6))))
E6=SUMPRODUCT((((we=E\$3)*(spec=\$B\$1)*(indicator=\$C\$6))))
F6=SUMPRODUCT((((we=F\$3)*(spec=\$B\$1)*(indicator=\$C\$6))))
G6=SUMPRODUCT((((we=G\$3)*(spec=\$B\$1)*(indicator=\$C\$6))))
D7=SUMPRODUCT((((we=D\$3)*(spec=\$B\$1)*(indicator=\$C\$7))))
E7=SUMPRODUCT((((we=E\$3)*(spec=\$B\$1)*(indicator=\$C\$7))))
F7=SUMPRODUCT((((we=F\$3)*(spec=\$B\$1)*(indicator=\$C\$7))))
G7=SUMPRODUCT((((we=G\$3)*(spec=\$B\$1)*(indicator=\$C\$7))))

</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
indicator=DATA!\$B:\$B
spec=DATA!\$D:\$D
we=DATA!\$A:\$A

</tbody>

<tbody>
</tbody>

Last edited:

Replies
1
Views
293
Replies
7
Views
294
Replies
0
Views
74
Replies
3
Views
314
Replies
1
Views
145

1,203,052
Messages
6,053,229
Members
444,648
Latest member
sinkuan85

### 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.

### Which adblocker are you using?

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

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