Work Project - Formula help needed!

[sjohnson77]

Hi Guys

I am a novice Excel user who is looking to automate my workload to save me time.

I am currently working on a financial project for my business and I have attached a screenshot to show what I am talking about.

I have my data which gets pulled automatically on the left and then I have my analysis tables ready on the right. So far I have automated the total spend column to calculate the total spend correctly depending on the year selected in the drop down (top table).

I now am working on a way to total the amounts in the admissions & Evidenced tables (Salary, properties investments etc.) and to collectively total the admissions/Evidence automatically in the top table corresponding to the correct year. Is there a formula I can use to automate this for me? I will be manually typing the admissions and evidenced values in anyway (middle table and bottom table on the right), but I want a way for the spreadsheet to calculate all the values collectively and to show the values in the table at the top once I have typed them in?

Any help would be greatly appreciated!

Kind regards

 

[JamesCanale]

MrExcelPlayground4.xlsx
	H	I	J	K	L	M
1	CY	Total Spend	Total Admissions	Total Evidenced
2	2017		$ 14,074,621	$ 21
3	2018		$ 6	$ -
4	2019		$ 3	$ 54
5	2020		$ 300	$ -
6	2021		$ 2,700	$ -
7
8
9	Admissions
10		2017	2018	2019	2020	2021
11	Salary	$ 55,000	$ 2			$ 200
12	Prop	$ 18,000	$ 1		$ 100	$ 300
13	Inv	$ 29,000	$ 3	$ 1	$ 200	$ 400
14	Sav	$ 5,555,555		$ 2		$ 500
15	Inher	$ 4,521				$ 600
16	Other	$ 8,412,545				$ 700
17
18	Evidenced
19		2017	2018	2019	2020	2021
20	Salary	$ 1		$ 7
21	Prop	$ 2		$ 8
22	Inv	$ 3		$ 9
23	Sav	$ 4		$ 9
24	Inher	$ 5		$ 10
25	Other	$ 6		$ 11
Sheet7

Cell Formulas
Range	Formula
J2:J6	J2	=SUM(INDEX($I$11:$M$16,{1,2,3,4,5,6},XMATCH(H2,$I$10:$M$10,0)))
K2:K6	K2	=SUM(INDEX($I$20:$M$25,{1,2,3,4,5,6},XMATCH(H2,$I$19:$M$19,0)))

 

[etaf]

probably with something like
=SUM(INDEX($I$8:$K$10,,MATCH($H2,$I$7:$K$7,0)))
Where I8:K10 is the data
AND
I7:K7
is the headers

Book1
	H	I	J	K
1
2	2018	60
3
4
5
6
7		2017	2018	2019
8	A	1	10	100
9	B	2	20	200
10	C	3	30	300
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(INDEX($I$8:$K$10,,MATCH($H2,$I$7:$K$7,0)))

 

[sjohnson77]

Great responses thank you both for your help! Etaf's was more user friendly for the novice in me I couldn't get James's to work unfortunately.

 

[etaf]

you are welcome