Index Match that will sum multiple outputs for a query

[Antares2000]

Hello guys!

I currently have a database with company info that is organized by quarters I would need to summarize by year. The database is roughly organized like the table below, but with a lot more inputs per company and doing SUMIF manually will be an task with no end. I tried using index match, but it won't sum multiple outputs...

INPUT (sheet input)

A B C D E F G H
[TABLE="width: 1167"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2020[/TD]
[TD]2020[/TD]
[TD]2020[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]3 Apple[/TD]
[TD]Revenues[/TD]
[TD="align: right"]100,00[/TD]
[TD="align: right"]102,00[/TD]
[TD="align: right"]104,00[/TD]
[TD="align: right"]106,00[/TD]
[TD="align: right"]108,00[/TD]
[TD="align: right"]110,00[/TD]
[TD="align: right"]112,00[/TD]
[TD="align: right"]114,00[/TD]
[TD="align: right"]116,00[/TD]
[TD="align: right"]118,00[/TD]
[TD="align: right"]120,00[/TD]
[TD="align: right"]122,00[/TD]
[TD="align: right"]124,00[/TD]
[TD="align: right"]126,00[/TD]
[TD="align: right"]128,00[/TD]
[TD="align: right"]130,00[/TD]
[/TR]
[TR]
[TD]4 Apple[/TD]
[TD]COGS[/TD]
[TD="align: right"]20,00[/TD]
[TD="align: right"]20,40[/TD]
[TD="align: right"]20,80[/TD]
[TD="align: right"]21,20[/TD]
[TD="align: right"]21,60[/TD]
[TD="align: right"]22,00[/TD]
[TD="align: right"]22,40[/TD]
[TD="align: right"]22,80[/TD]
[TD="align: right"]23,20[/TD]
[TD="align: right"]23,60[/TD]
[TD="align: right"]24,00[/TD]
[TD="align: right"]24,40[/TD]
[TD="align: right"]24,80[/TD]
[TD="align: right"]25,20[/TD]
[TD="align: right"]25,60[/TD]
[TD="align: right"]26,00[/TD]
[/TR]
[TR]
[TD]5 Apple[/TD]
[TD]SG&A[/TD]
[TD="align: right"]10,00[/TD]
[TD="align: right"]10,20[/TD]
[TD="align: right"]10,40[/TD]
[TD="align: right"]10,60[/TD]
[TD="align: right"]10,80[/TD]
[TD="align: right"]11,00[/TD]
[TD="align: right"]11,20[/TD]
[TD="align: right"]11,40[/TD]
[TD="align: right"]11,60[/TD]
[TD="align: right"]11,80[/TD]
[TD="align: right"]12,00[/TD]
[TD="align: right"]12,20[/TD]
[TD="align: right"]12,40[/TD]
[TD="align: right"]12,60[/TD]
[TD="align: right"]12,80[/TD]
[TD="align: right"]13,00[/TD]
[/TR]
[TR]
[TD]6 Apple[/TD]
[TD]EBITDA[/TD]
[TD="align: right"]70,00[/TD]
[TD="align: right"]71,40[/TD]
[TD="align: right"]72,80[/TD]
[TD="align: right"]74,20[/TD]
[TD="align: right"]75,60[/TD]
[TD="align: right"]77,00[/TD]
[TD="align: right"]78,40[/TD]
[TD="align: right"]79,80[/TD]
[TD="align: right"]81,20[/TD]
[TD="align: right"]82,60[/TD]
[TD="align: right"]84,00[/TD]
[TD="align: right"]85,40[/TD]
[TD="align: right"]86,80[/TD]
[TD="align: right"]88,20[/TD]
[TD="align: right"]89,60[/TD]
[TD="align: right"]91,00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8 Amazon[/TD]
[TD]Revenues[/TD]
[TD="align: right"]70,00[/TD]
[TD="align: right"]72,00[/TD]
[TD="align: right"]74,00[/TD]
[TD="align: right"]76,00[/TD]
[TD="align: right"]78,00[/TD]
[TD="align: right"]80,00[/TD]
[TD="align: right"]82,00[/TD]
[TD="align: right"]84,00[/TD]
[TD="align: right"]86,00[/TD]
[TD="align: right"]88,00[/TD]
[TD="align: right"]90,00[/TD]
[TD="align: right"]92,00[/TD]
[TD="align: right"]94,00[/TD]
[TD="align: right"]96,00[/TD]
[TD="align: right"]98,00[/TD]
[TD="align: right"]100,00[/TD]
[/TR]
[TR]
[TD]9 Amazon[/TD]
[TD]COGS[/TD]
[TD="align: right"]14,00[/TD]
[TD="align: right"]14,40[/TD]
[TD="align: right"]14,80[/TD]
[TD="align: right"]15,20[/TD]
[TD="align: right"]15,60[/TD]
[TD="align: right"]16,00[/TD]
[TD="align: right"]16,40[/TD]
[TD="align: right"]16,80[/TD]
[TD="align: right"]17,20[/TD]
[TD="align: right"]17,60[/TD]
[TD="align: right"]18,00[/TD]
[TD="align: right"]18,40[/TD]
[TD="align: right"]18,80[/TD]
[TD="align: right"]19,20[/TD]
[TD="align: right"]19,60[/TD]
[TD="align: right"]20,00[/TD]
[/TR]
[TR]
[TD]10Amazon[/TD]
[TD]SG&A[/TD]
[TD="align: right"]7,00[/TD]
[TD="align: right"]7,20[/TD]
[TD="align: right"]7,40[/TD]
[TD="align: right"]7,60[/TD]
[TD="align: right"]7,80[/TD]
[TD="align: right"]8,00[/TD]
[TD="align: right"]8,20[/TD]
[TD="align: right"]8,40[/TD]
[TD="align: right"]8,60[/TD]
[TD="align: right"]8,80[/TD]
[TD="align: right"]9,00[/TD]
[TD="align: right"]9,20[/TD]
[TD="align: right"]9,40[/TD]
[TD="align: right"]9,60[/TD]
[TD="align: right"]9,80[/TD]
[TD="align: right"]10,00[/TD]
[/TR]
[TR]
[TD]12Amazon[/TD]
[TD]EBITDA[/TD]
[TD="align: right"]49,00[/TD]
[TD="align: right"]50,40[/TD]
[TD="align: right"]51,80[/TD]
[TD="align: right"]53,20[/TD]
[TD="align: right"]54,60[/TD]
[TD="align: right"]56,00[/TD]
[TD="align: right"]57,40[/TD]
[TD="align: right"]58,80[/TD]
[TD="align: right"]60,20[/TD]
[TD="align: right"]61,60[/TD]
[TD="align: right"]63,00[/TD]
[TD="align: right"]64,40[/TD]
[TD="align: right"]65,80[/TD]
[TD="align: right"]67,20[/TD]
[TD="align: right"]68,60[/TD]
[TD="align: right"]70,00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

DESIRED OUTPUT (sheet output)

A B C D E F G
1 Company Amazon
[TABLE="width: 448"]
<tbody>[TR]
[TD]2[/TD]
[TD] 2017[/TD]
[TD] 2018[/TD]
[TD] 2019[/TD]
[TD] 2020[/TD]
[TD] 2021[/TD]
[TD] 2022[/TD]
[/TR]
[TR]
[TD="colspan: 2"]3 Revenues[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 COGS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 SG&A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For example, the formula would be inputed in B3, look for "Revenues" (A3) in column B (B:B) of sheet input, "Amazon" ($B$1) at column A (A:A) of sheet input and "2017" (B2) in row 1 (1:1) of sheet input, the problem is that there are four values for 2017 in row 1 of sheet input. Is there a way that the output of the formula will sum all such numbers for 2017?

In this example the result would be 412,00

I could also compile columns A and B in input to reduce the number of variables if this will avoid array formulas. I really appreciate any help!

Thanks!

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]IMPUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

[/TD]
[TD]

2019​

[/TD]
[TD]

2019​

[/TD]
[TD]

2019​

[/TD]
[TD]

2019​

[/TD]
[TD]

2020​

[/TD]
[TD]

2020​

[/TD]
[TD]

2020​

[/TD]
[TD]

2020​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] Apple[/TD]
[TD]Revenues[/TD]
[TD]

100.00​

[/TD]
[TD]

102.00​

[/TD]
[TD]

104.00​

[/TD]
[TD]

106.00​

[/TD]
[TD]

108.00​

[/TD]
[TD]

110.00​

[/TD]
[TD]

112.00​

[/TD]
[TD]

114.00​

[/TD]
[TD]

116.00​

[/TD]
[TD]

118.00​

[/TD]
[TD]

120.00​

[/TD]
[TD]

122.00​

[/TD]
[TD]

124.00​

[/TD]
[TD]

126.00​

[/TD]
[TD]

128.00​

[/TD]
[TD]

130.00​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] Apple[/TD]
[TD]COGS[/TD]
[TD]

20.00​

[/TD]
[TD]

20.40​

[/TD]
[TD]

20.80​

[/TD]
[TD]

21.20​

[/TD]
[TD]

21.60​

[/TD]
[TD]

22.00​

[/TD]
[TD]

22.40​

[/TD]
[TD]

22.80​

[/TD]
[TD]

23.20​

[/TD]
[TD]

23.60​

[/TD]
[TD]

24.00​

[/TD]
[TD]

24.40​

[/TD]
[TD]

24.80​

[/TD]
[TD]

25.20​

[/TD]
[TD]

25.60​

[/TD]
[TD]

26.00​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] Apple[/TD]
[TD]SG&A[/TD]
[TD]

10.00​

[/TD]
[TD]

10.20​

[/TD]
[TD]

10.40​

[/TD]
[TD]

10.60​

[/TD]
[TD]

10.80​

[/TD]
[TD]

11.00​

[/TD]
[TD]

11.20​

[/TD]
[TD]

11.40​

[/TD]
[TD]

11.60​

[/TD]
[TD]

11.80​

[/TD]
[TD]

12.00​

[/TD]
[TD]

12.20​

[/TD]
[TD]

12.40​

[/TD]
[TD]

12.60​

[/TD]
[TD]

12.80​

[/TD]
[TD]

13.00​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] Apple[/TD]
[TD]EBITDA[/TD]
[TD]

70.00​

[/TD]
[TD]

71.40​

[/TD]
[TD]

72.80​

[/TD]
[TD]

74.20​

[/TD]
[TD]

75.60​

[/TD]
[TD]

77.00​

[/TD]
[TD]

78.40​

[/TD]
[TD]

79.80​

[/TD]
[TD]

81.20​

[/TD]
[TD]

82.60​

[/TD]
[TD]

84.00​

[/TD]
[TD]

85.40​

[/TD]
[TD]

86.80​

[/TD]
[TD]

88.20​

[/TD]
[TD]

89.60​

[/TD]
[TD]

91.00​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Amazon[/TD]
[TD]Revenues[/TD]
[TD]

70.00​

[/TD]
[TD]

72.00​

[/TD]
[TD]

74.00​

[/TD]
[TD]

76.00​

[/TD]
[TD]

78.00​

[/TD]
[TD]

80.00​

[/TD]
[TD]

82.00​

[/TD]
[TD]

84.00​

[/TD]
[TD]

86.00​

[/TD]
[TD]

88.00​

[/TD]
[TD]

90.00​

[/TD]
[TD]

92.00​

[/TD]
[TD]

94.00​

[/TD]
[TD]

96.00​

[/TD]
[TD]

98.00​

[/TD]
[TD]

100.00​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Amazon[/TD]
[TD]COGS[/TD]
[TD]

14.00​

[/TD]
[TD]

14.40​

[/TD]
[TD]

14.80​

[/TD]
[TD]

15.20​

[/TD]
[TD]

15.60​

[/TD]
[TD]

16.00​

[/TD]
[TD]

16.40​

[/TD]
[TD]

16.80​

[/TD]
[TD]

17.20​

[/TD]
[TD]

17.60​

[/TD]
[TD]

18.00​

[/TD]
[TD]

18.40​

[/TD]
[TD]

18.80​

[/TD]
[TD]

19.20​

[/TD]
[TD]

19.60​

[/TD]
[TD]

20.00​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Amazon[/TD]
[TD]SG&A[/TD]
[TD]

7.00​

[/TD]
[TD]

7.20​

[/TD]
[TD]

7.40​

[/TD]
[TD]

7.60​

[/TD]
[TD]

7.80​

[/TD]
[TD]

8.00​

[/TD]
[TD]

8.20​

[/TD]
[TD]

8.40​

[/TD]
[TD]

8.60​

[/TD]
[TD]

8.80​

[/TD]
[TD]

9.00​

[/TD]
[TD]

9.20​

[/TD]
[TD]

9.40​

[/TD]
[TD]

9.60​

[/TD]
[TD]

9.80​

[/TD]
[TD]

10.00​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Amazon[/TD]
[TD]EBITDA[/TD]
[TD]

49.00​

[/TD]
[TD]

50.40​

[/TD]
[TD]

51.80​

[/TD]
[TD]

53.20​

[/TD]
[TD]

54.60​

[/TD]
[TD]

56.00​

[/TD]
[TD]

57.40​

[/TD]
[TD]

58.80​

[/TD]
[TD]

60.20​

[/TD]
[TD]

61.60​

[/TD]
[TD]

63.00​

[/TD]
[TD]

64.40​

[/TD]
[TD]

65.80​

[/TD]
[TD]

67.20​

[/TD]
[TD]

68.60​

[/TD]
[TD]

70.00​

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Company[/TD]
[TD]Amazon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD]

2019​

[/TD]
[TD]

2020​

[/TD]
[TD]

2021​

[/TD]
[TD]

2022​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Revenues[/TD]
[TD]

292​

[/TD]
[TD]

324​

[/TD]
[TD]

356​

[/TD]
[TD]

388​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]COGS[/TD]
[TD]

58.4​

[/TD]
[TD]

64.8​

[/TD]
[TD]

71.2​

[/TD]
[TD]

77.6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]SG&A[/TD]
[TD]

29.2​

[/TD]
[TD]

32.4​

[/TD]
[TD]

35.6​

[/TD]
[TD]

38.8​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]EBITDA[/TD]
[TD]

204.4​

[/TD]
[TD]

226.8​

[/TD]
[TD]

249.2​

[/TD]
[TD]

271.6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet Output

B3 =SUMPRODUCT((IMPUT!$A$3:$A$11=OUTPUT!$B$1)*(IMPUT!$B$3:$B$11=OUTPUT!$A3)*(IMPUT!$C$2:$R$2=OUTPUT!B$2)*(ISNUMBER(IMPUT!$C$3:$R$11)),(IMPUT!$C$3:$R$11))

copy across and down