Sumproduct Index Match Maybe?

[rehberger]

Hello, I am trying to come up with a formula that i can drag to sum just the weekdays from the data set up below:

	Weekdays
	2019
	Rooms	Revenue
Casino	Formula	Formula
Events	Formula	Formula

<tbody>
</tbody>

Data looks like this:

	Casino	Casino	Events	Events
	2019	2019	2019	2019
	Rooms	Revenue	Rooms	Revenue
1/1/19	10	200	20	400
1/2/19	15	300	22	300
1/3/19	12	500	20	450
1/4/19	5	100	10	300
1/5/19	10	200	15	200
1/6/19	15	300	15	200

<tbody>
</tbody>

 

[JoeMo]

Excel Workbook
	A	B	C	D	E
1	Weekdays
2		2019
3		Rooms	Revenue
4	Casino	42	1100
5	Events	72	1450
6
7
8
9		Casino	Casino	Events	Events
10		2019	2019	2019	2019
11		Rooms	Revenue	Rooms	Revenue
12	1/1/2019	10	200	20	400
13	1/2/2019	15	300	22	300
14	1/3/2019	12	500	20	450
15	1/4/2019	5	100	10	300
16	1/5/2019	10	200	15	200
17	1/6/2019	15	300	15	200
Sheet5

 

[jtakw]

Hi,

If I understand correctly.

Change/adjust cell references/range as needed, formula copied down and across:

Book1
	A	B	C	D	E	F	G	H	I
1	Weekdays					Casino	Casino	Events	Events
2		2019				2019	2019	2019	2019
3		Rooms	Revenue			Rooms	Revenue	Rooms	Revenue
4	Casino	42	1100		1/1/2019	10	200	20	400
5	Events	72	1450		1/2/2019	15	300	22	300
6					1/3/2019	12	500	20	450
7					1/4/2019	5	100	10	300
8					1/5/2019	10	200	15	200
9					1/6/2019	15	300	15	200
Sheet549

Cell Formulas
Range	Formula
B4	=SUMPRODUCT(($F$1:$I$1=$A4)*($F$2:$I$2=$B$2)*($F$3:$I$3=B$3)*(WEEKDAY($E$4:$E$9,2)<6)*$F$4:$I$9)

 

[rehberger]

Works great! thanks!

 

[jtakw]

You're welcome.