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

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

<tbody>
</tbody>

DESIRED OUTPUT (sheet output)

A B C D E F G
1 Company Amazon

2	2017	2018	2019	2020	2021	2022
3 Revenues
4 COGS
5 SG&A
6 EBITDA

<tbody>
</tbody>

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]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

IMPUT

2​

2017​

2017​

2017​

2017​

2018​

2018​

2018​

2018​

2019​

2019​

2019​

2019​

2020​

2020​

2020​

2020​

3​

Q1​

Q2​

Q3​

Q4​

Q1​

Q2​

Q3​

Q4​

Q1​

Q2​

Q3​

Q4​

Q1​

Q2​

Q3​

Q4​

4​

Apple

Revenues

100.00​

102.00​

104.00​

106.00​

108.00​

110.00​

112.00​

114.00​

116.00​

118.00​

120.00​

122.00​

124.00​

126.00​

128.00​

130.00​

5​

Apple

COGS

20.00​

20.40​

20.80​

21.20​

21.60​

22.00​

22.40​

22.80​

23.20​

23.60​

24.00​

24.40​

24.80​

25.20​

25.60​

26.00​

6​

Apple

SG&A

10.00​

10.20​

10.40​

10.60​

10.80​

11.00​

11.20​

11.40​

11.60​

11.80​

12.00​

12.20​

12.40​

12.60​

12.80​

13.00​

7​

Apple

EBITDA

70.00​

71.40​

72.80​

74.20​

75.60​

77.00​

78.40​

79.80​

81.20​

82.60​

84.00​

85.40​

86.80​

88.20​

89.60​

91.00​

8​

Amazon

Revenues

70.00​

72.00​

74.00​

76.00​

78.00​

80.00​

82.00​

84.00​

86.00​

88.00​

90.00​

92.00​

94.00​

96.00​

98.00​

100.00​

9​

Amazon

COGS

14.00​

14.40​

14.80​

15.20​

15.60​

16.00​

16.40​

16.80​

17.20​

17.60​

18.00​

18.40​

18.80​

19.20​

19.60​

20.00​

10​

Amazon

SG&A

7.00​

7.20​

7.40​

7.60​

7.80​

8.00​

8.20​

8.40​

8.60​

8.80​

9.00​

9.20​

9.40​

9.60​

9.80​

10.00​

11​

Amazon

EBITDA

49.00​

50.40​

51.80​

53.20​

54.60​

56.00​

57.40​

58.80​

60.20​

61.60​

63.00​

64.40​

65.80​

67.20​

68.60​

70.00​

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Company	Amazon							OUTPUT
2​		2017​	2018​	2019​	2020​	2021​	2022​
3​	Revenues	292​	324​	356​	388​	0​	0​
4​	COGS	58.4​	64.8​	71.2​	77.6​	0​	0​
5​	SG&A	29.2​	32.4​	35.6​	38.8​	0​	0​
6​	EBITDA	204.4​	226.8​	249.2​	271.6​	0​	0​

<tbody>
</tbody>

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