Sumif multiple criteria with index and match no table

[afterthefall28]

I am basically looking for a dynamic sumif formula that finds the criteria of Hotel and month then returns the sum of multiple rows based on the Mapping (from data tab to output tab). I want to be able to change the hotel and month on the output tab and it automatically pulls in the correct sum. The data is not in table format and that is not an option.

Data Tab

	Hotel A	Hotel A	Hotel A		Hotel B	Hotel B	Hotel B
	Jan	Feb	Mar		Jan	Feb	Mar
MAP_Rev	50,000	60,000	40,000		25,000	10,000	30,000
MAP_Exp	(25,000)	(20,000)	(25,000)		(10,000)	(5,000)	(4,000)
MAP_Exp	(3,000)	(4,000)	(2,000)		(3,000)	(1,000)	(3,000)
MAP_Exp	(400)	(1,000)	(300)		-	(200)	(400)

Output Tab

Match 1	Mapping		Jan	<- Match 2
Hotel A	MAP_Rev	Revenue	FORMULA HERE
Hotel A	MAP_Exp	Expense	FORMULA HERE

 

[Eric W]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H
1		Hotel A	Hotel A	Hotel A		Hotel B	Hotel B	Hotel B
2		Jan	Feb	Mar		Jan	Feb	Mar
3	MAP_Rev	50,000	60,000	40,000		25,000	10,000	30,000
4
5	MAP_Exp	-25,000	-20,000	-25,000		-10,000	-5,000	-4,000
6	MAP_Exp	-3,000	-4,000	-2,000		-3,000	-1,000	-3,000
7	MAP_Exp	-400	-1,000	-300		0	-200	-400
8
9	Output Tab
10	Match 1	Mapping		Jan	Feb	Mar
11	Hotel A	MAP_Rev	Revenue	50000	60000	40000
12	Hotel A	MAP_Exp	Expense	-28400	-25000	-27300
13	Hotel B	MAP_Rev	Revenue	25000	10000	30000
14	Hotel B	MAP_Exp	Expense	-13000	-6200	-7400
D1

Cell Formulas
Range	Formula
D11:F14	D11	=SUMPRODUCT($B$3:$H$7*($A$3:$A$7=$B11)*($B$1:$H$1=$A11)*($B$2:$H$2=D$10))

 

[afterthefall28]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H
1		Hotel A	Hotel A	Hotel A		Hotel B	Hotel B	Hotel B
2		Jan	Feb	Mar		Jan	Feb	Mar
3	MAP_Rev	50,000	60,000	40,000		25,000	10,000	30,000
4
5	MAP_Exp	-25,000	-20,000	-25,000		-10,000	-5,000	-4,000
6	MAP_Exp	-3,000	-4,000	-2,000		-3,000	-1,000	-3,000
7	MAP_Exp	-400	-1,000	-300		0	-200	-400
8
9	Output Tab
10	Match 1	Mapping		Jan	Feb	Mar
11	Hotel A	MAP_Rev	Revenue	50000	60000	40000
12	Hotel A	MAP_Exp	Expense	-28400	-25000	-27300
13	Hotel B	MAP_Rev	Revenue	25000	10000	30000
14	Hotel B	MAP_Exp	Expense	-13000	-6200	-7400
D1

Cell Formulas
Range	Formula
D11:F14	D11	=SUMPRODUCT($B$3:$H$7*($A$3:$A$7=$B11)*($B$1:$H$1=$A11)*($B$2:$H$2=D$10))

This worked for me. Thanks!