Alternative to Index Match to sum up

[colzre]

Hello Experts,

I am after a formula to replace the current Index Match I am using at the moment.

Would you know an alternative option for same result?

Formula KPIs.xlsm
	A	B	C	D	E
1	6		June
2
3		COMPANY
4
5				BUDGET
6		Rooms Available		1,740
7		Rooms Sold		1,730
8
KPIs

Cell Formulas
Range	Formula
C1	C1	=TEXT(A1*28, "MMMMMMMM")
D6	D6	=IFNA(SUM(INDEX('BUDGET 2024'!$B$8:$M$9,0,MATCH($A$1,'BUDGET 2024'!$B$1:$M$1,0))*($B$6='BUDGET 2024'!$X$8:$X$9)),"0")
D7	D7	=IFNA(SUM(INDEX('BUDGET 2024'!$B$8:$M$9,0,MATCH($A$1,'BUDGET 2024'!$B$1:$M$1,0))*($B$7='BUDGET 2024'!$X$8:$X$9)),"0")

Formula KPIs.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		1	2	3	4	5	6	7	8	9	10	11	12
2
3
4
5
6
7
8	KPI 1	1,798	1,682	1,798	1,740	1,798	1,740	1,798	1,798	1,740	1,798	1,740	1,798	21,228										Rooms Available
9	KPI 2	1,205	1,250	1,680	1,640	1,780	1,730	1,770	1,750	1,690	1,755	1,435	1,280	18,965										Rooms Sold
10
BUDGET 2024

Thank you beforehand!

John

 

[Fluff]

As you are just returning a single value, how about

=IFNA(INDEX('BUDGET 2024'!$B$8:$M$9,match($B$6,'BUDGET 2024'!$X$8:$X$9,0),MATCH($A$1,'BUDGET 2024'!$B$1:$M$1,0)),0)

 

[colzre]

As you are just returning a single value, how about

=IFNA(INDEX('BUDGET 2024'!$B$8:$M$9,match($B$6,'BUDGET 2024'!$X$8:$X$9,0),MATCH($A$1,'BUDGET 2024'!$B$1:$M$1,0)),0)

This formula gives me some problems depending on the users. Some people get error Value# and some not (like my case), which doesn´t happen with any other function before. Still better if this sums up as there might be the same single value in different lines of the sheet.

Hence, a different way to get to same end would be welcome (Avoiding Index Match).


Thanks,
Juan

 

[Fluff]

An index/match combo should not give a #value error, unless you either have that in your data, or the formula is wrong.
If they are incorrectly entering the formula, it doesn't matter what you change it to, as they will probably get that wrong as well.

 

[Fluff]

You could use sumproduct like

=SUMPRODUCT(('BUDGET 2024'!$B$1:$M$1=$A$1)*('BUDGET 2024'!$X$8:$X$9=$B$6)*('BUDGET 2024'!$B$8:$M$9))