Antares2000
New Member
- Joined
- Dec 31, 2017
- Messages
- 1
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
<tbody>
</tbody>
DESIRED OUTPUT (sheet output)
A B C D E F G
1 Company Amazon
<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!
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!