simplification of formula?

[Akakaboto]

Hello,

I would like to know if you can figure out an easier way to write below formula. Previuosly we have had a toolbox that that was supporting this type of calculations but because of migration to excel 2013 we noticed that tool is no longer working so I need to teach ppl how they can replace the toolbox formulas (most users have only basic knowledge of excel (like vlookup, sumif/s etc.). I do not want a formula that require helper columns

example.

table in A1:H15
[TABLE="width: 514"]
<tbody>[TR]
[TD]Product Group
[/TD]
[TD]Org Unit
[/TD]
[TD]Item
[/TD]
[TD]P8
[/TD]
[TD]P9
[/TD]
[TD]P10
[/TD]
[TD]P11
[/TD]
[TD]P12
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3A7
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]83
[/TD]
[TD="align: right"]160
[/TD]
[TD="align: right"]179
[/TD]
[TD="align: right"]179
[/TD]
[TD="align: right"]333
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3A7
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]174
[/TD]
[TD="align: right"]189
[/TD]
[TD="align: right"]260
[/TD]
[TD="align: right"]307
[/TD]
[TD="align: right"]283
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3ANN
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]17
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]46
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3ANN
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]-7
[/TD]
[TD="align: right"]-5
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3ANP
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]-48
[/TD]
[TD="align: right"]-22
[/TD]
[TD="align: right"]-16
[/TD]
[TD="align: right"]-16
[/TD]
[TD="align: right"]35
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3ANP
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]-83
[/TD]
[TD="align: right"]-79
[/TD]
[TD="align: right"]-61
[/TD]
[TD="align: right"]-49
[/TD]
[TD="align: right"]-55
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3EN
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]-127
[/TD]
[TD="align: right"]-117
[/TD]
[TD="align: right"]-71
[/TD]
[TD="align: right"]-41
[/TD]
[TD="align: right"]-56
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3EW2
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]134
[/TD]
[TD="align: right"]165
[/TD]
[TD="align: right"]165
[/TD]
[TD="align: right"]411
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3EW2
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]-163
[/TD]
[TD="align: right"]-150
[/TD]
[TD="align: right"]-89
[/TD]
[TD="align: right"]-49
[/TD]
[TD="align: right"]-69
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3EL
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]58
[/TD]
[TD="align: right"]93
[/TD]
[TD="align: right"]102
[/TD]
[TD="align: right"]102
[/TD]
[TD="align: right"]172
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3EL
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]74
[/TD]
[TD="align: right"]79
[/TD]
[TD="align: right"]103
[/TD]
[TD="align: right"]118
[/TD]
[TD="align: right"]110
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]100045
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]-416
[/TD]
[TD="align: right"]-1057
[/TD]
[TD="align: right"]-1216
[/TD]
[TD="align: right"]-1216
[/TD]
[TD="align: right"]-2496
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]100045
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]-465
[/TD]
[TD="align: right"]-562
[/TD]
[TD="align: right"]-1020
[/TD]
[TD="align: right"]-1319
[/TD]
[TD="align: right"]-1166
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3IAO
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"]-3
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]63
[/TD]
[TD="align: right"]63
[/TD]
[TD="align: right"]169
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3IAO
[/TD]
[TD]3344299000
[/TD]
[TD="align: right"]107
[/TD]
[TD="align: right"]117
[/TD]
[TD="align: right"]164
[/TD]
[TD="align: right"]194
[/TD]
[TD="align: right"]179
[/TD]
[/TR]
</tbody>[/TABLE]


I need to sum the data based on all three columns J:L and Period value in M1

table J1:M3
[TABLE="width: 290"]
<tbody>[TR]
[TD]Product Group
[/TD]
[TD]Org Unit
[/TD]
[TD]Item
[/TD]
[TD]P10
[/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]3A7
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3PCS
[/TD]
[TD]100045
[/TD]
[TD]3344120000
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I came up with this formula =SUMPRODUCT(--($A$2:$A$25&$B$2:$B$25&$C$2:$C$25=$J2&$K2&$L2);INDEX($D$2:$H$25;;MATCH(M$1;$D$1:$H$1;0)))
that seems to work but I would like to know if it can be written in an easier and/or better way.

 

[Eric W]

This should also work for you:

=SUMIFS(INDEX($D:$H;;MATCH(M$1;$D$1:$H$1;0));A:A;J2;B:B;K2;C:C;L2)