Matching Logic Function

[Walker_Ice]

Hi everyone,

I have a minor issue and I'm not sure the best way to solve this problem. I believe I have to use a mixture of maybe the MATCH & INDEX function with some logic as well but I wanted to see if anyone had any ideas on how to go about this.

I'm trying to make so that based on the month and year, selected, it will divide the number of appointments by the amount of money made in the corresponding month and year from the chart. If "All" is selected it will add the revenue from all the years for that month.

ANy help would be greatly appreciated.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Business Analysis
2	Monthly Income Report		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
3	2022		$0.00	$0.00	$0.00	$4,300.00	$88,888.00	$0.00	$900.00	$750.00	$999.00	$0.00	$0.00	$0.00
4	2023		$1,400.00	$3,890.00	$4,555.00	$70,576.00	$16,588.00	$1,385.00	$4,700.00	$1,406.00	$15,000.00	$30,090.00	$50,000.00	$20,000.00
5	2024		$0.00	$0.00	$6,800.00	$0.00	$0.00	$0.00	$0.00	$0.00	$40,000.00	$0.00	$0.00	$0.00
6	2025		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
7
8
9
10
11
12							Select Year:			All
13							Month:	# of Bookings	Avg price per Booking
14							JAN	50	$28.00
15							FEB	103	$37.77
16							MAR	149	$30.57
17							APR	309	$228.40
18							MAY	233	$71.19
19							JUN	179	$7.74
20							JUL	745	$6.31
21							AUG	267	$5.27
22							SEP	92	$163.04
23							OCT	2078	$14.48
24							NOV	80	$625.00
25							DEC	987	$20.26
Sheet1

Cell Formulas
Range	Formula
I14	I14	=C4/H14
I15	I15	=D4/H15
I16	I16	=E4/H16
I17	I17	=F4/H17
I18	I18	=G4/H18
I19	I19	=H4/H19
I20	I20	=I4/H20
I21	I21	=J4/H21
I22	I22	=K4/H22
I23	I23	=L4/H23
I24	I24	=M4/H24
I25	I25	=N4/H25

Cells with Data Validation
Cell	Allow	Criteria
J12	List	All, 2022, 2023,2024,2025

 

[Muhammad_Usman]

1235.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Business Analysis
2	Monthly Income Report		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
3	2022		0	0	0	4300	88888	0	900	750	999	0	0	0
4	2023		1400	3890	4555	70576	16588	1385	4700	1406	15000	30090	50000	20000
5	2024		0	0	6800	0	0	0	0	0	40000	0	0	0
6	2025		0	0	0	0	0	0	0	0	0	0	0	0
7	ALL		1400	3890	11355	74876	105476	1385	5600	2156	55999	30090	50000	20000
8
9
10
11
12							Select Year:			ALL
13							Month:	# of Bookings	Avg price per Booking
14							JAN	50	28.00
15							FEB	103	37.77
16							MAR	149	76.21
17							APR	309	242.32
18							MAY	233	452.69
19							JUN	179	7.74
20							JUL	745	7.52
21							AUG	267	8.07
22							SEP	92	608.68
23							OCT	2078	14.48
24							NOV	80	625.00
25							DEC	987	20.26
Sheet20

Cell Formulas
Range	Formula
C7:N7	C7	=SUM(C3:C6)
I14:I25	I14	=INDEX($A$2:$N$7,MATCH($J$12,$A$2:$A$7,0),MATCH(G14,$A$2:$N$2,0))/H14

Cells with Data Validation
Cell	Allow	Criteria
J12	List	=$A$3:$A$7

 

[FormR]

Hi, here's another option that doesn't need "All" total row.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Business Analysis
2	Monthly Income Report		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
3	2022		0	0	0	4300	88888	0	900	750	999	0	0	0
4	2023		1400	3890	4555	70576	16588	1385	4700	1406	15000	30090	50000	20000
5	2024		0	0	6800	0	0	0	0	0	40000	0	0	0
6	2025		0	0	0	0	0	0	0	0	0	0	0	0
7
8
9
10
11
12							Select Year:			All
13							Month:	# of Bookings	Avg price per Booking
14							JAN	50	28
15							FEB	103	37.767
16							MAR	149	76.2081
17							APR	309	242.317
18							MAY	233	452.687
19							JUN	179	7.73743
20							JUL	745	7.51678
21							AUG	267	8.07491
22							SEP	92	608.685
23							OCT	2078	14.4803
24							NOV	80	625
25							DEC	987	20.2634
Sheet1

Cell Formulas
Range	Formula
I14:I25	I14	=SUM(INDEX($C$3:$N$6,IF($J$12="All",0,MATCH($J$12,$A$3:$A$6,0)),MATCH($G14,$C$2:$N$2,0)))/H14