help with SUMPRODUCT or an alternative solution

[J4L]

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.

 

[J4L]

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

	A	B	C	D	E	F
1	Week_Ending	INDICATOR	Classification	Main_Spec	Total	Plan_Figure
2	2015-04-05	N	DC	Grade - A	3	100
3	2015-04-05	N	IP	Grade - A	21	100
4	2015-04-05	E	DC	Grade - A	30	100
5	2015-04-05	E	IP	Grade - A	2	100
6	2015-04-12	E	Converted	Grade - A	6	100
7	2015-04-12	N	DC	Grade - A	5	100
8	2015-04-12	E	DC	Grade - A	78	100
9	2015-04-12	N	IP	Grade - A	27	100
10	2015-04-12	E	IP	Grade - A	8	100
11	2015-04-19	N	DC	Grade - A	8	100
12	2015-04-19	N	IP	Grade - A	31	100
13	2015-04-19	E	Converted	Grade - A	4	100
14	2015-04-19	E	DC	Grade - A	90	100
15	2015-04-19	E	IP	Grade - A	12	100
16	2015-04-26	E	Converted	Grade - A	2	100
17	2015-04-26	E	DC	Grade - A	83	100
18	2015-04-26	E	IP	Grade - A	11	100
19	2015-04-26	N	IP	Grade - A	42	100
20	2015-04-26	N	DC	Grade - A	4	100

<tbody>
</tbody>

DATA​

Excel 2007

	A	B	C	D	E	F	G	H
1		Grade - A
2
3				2015-04-05	2015-04-12	2015-04-19	2015-04-26
4			E	2	3	3	3
5			DC	0	0	0	0
6			N	2	2	2	2
7			DC	0	0	0	0
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula 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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To indicator =DATA!$B:$B spec =DATA!$D:$D we =DATA!$A:$A <thead> </thead><tbody> </tbody>

<tbody>
</tbody>