INDEX/MATCH and SUMIFS with Tables

I have a table named FINAL and one called DAILY. I have three columns in FINAL called Breakfast, Lunch, & Supper. For each row (location), the data in the columns is either the desired meal (dry cereal, fruit, etc.) or three dashes "---" if no meal is needed. There is also a column with Projected # attendees. Then there are columns for each day (Jun 5, Jun 6, etc.) with a Y if that row indicates that there will be a meeting that day. There are multiple locations with more than one program daily with different projected attendees, so I want to get the totals in 4 ways: total # expected on site for that day, # needing breakfast, lunch, & supper.

I have this in DAILY for the three meals:
=SUMIFS(FINAL['# Attendees],FINAL[ORG],[@ORG],FINAL[Breakfast],"<>---",FINAL[5-Jun],"=Y")
=SUMIFS(FINAL['# Attendees],FINAL[ORG],[@ORG],FINAL[Luncht],"<>---",FINAL[5-Jun],"=Y")
=SUMIFS(FINAL['# Attendees],FINAL[ORG],[@ORG],FINAL[Supper],"<>---",FINAL[5-Jun],"=Y")

This makes my DAILY table really wide...and I'm only going three months ahead right now. Plus I lose the date functionality for slicers since I have to add the meal type into the column header...no longer is it just Jun 5...now it has to be Jun 5 Bkfst, Jun 5 Lnch, Jun 5 Sppr, Jun 5 # atts.

I would like to have my DAILY table be viewable with slicers for the date range in a chart or PowerView.

So I think I might need to add a new column on DAILY for the criteria of meal. Then each location would have four entries only - Breakfast, Lunch, Supper, and Projected # Attendees for the day. I could then just have each day as a column header that would keep the date formatting so I could use slicers. But now, I need the formulas reworked.

I think I need an INDEX/MATCH scenario...I don't know how to refer to a column on another table by looking at a cell in the current table...

ORG	MEAL	Jun 5	Jun 6
Adams	Breakfast
Adams	Lunch
Adams	Supper
Adams	Projected Atts

I figure that if I could reference the actual text in the MEAL column as a column header for the other table, that would work...but I don't know how.

Any ideas would be appreciated.

OK - I think I have given you enough with this attachment.

I'd like to get a final chart or PowerView that can filter by date. I'm interested in totals for each meal type and total expected to attend regardless of meal.

My mind sees it as a column chart with the horizontal axis being the Org and the height indicating the # expected. Possibly a slicer for meal and another for the date.

See the attached Sheet1: is this what is required. I am not a user of Tables so your knowledge is likely to be better than mine: in particular dragging formulae across and down rows.

In Column A

=INDEX(DAILY[ORG],INT((ROWS($1:1)-1)/4)+1)

in column B

=INDEX(Lookups!$DO$2:$DO$5,MOD(ROWS($1:1)-1,4)+1)

DO2:DO5 contains table of headings

in Column C

=SUMIFS(FINAL[['# Stus]:['# Stus]],FINAL[[ORG]:[ORG]],$A3,FINAL[[Breakfast]:[Breakfast]],"<>---",FINAL[5-Jun],"=Y")

Not sure this is correct for dragging formula across columns !

Change "Breakfast" to "Lunch" etc for other rows: Can "Breakfast" in the formula be replaced with a cell reference (for my own education).?

John, I'm going to need a while to try and figure out what exactly your formulae are doing...but it does seem to be working!

My concern is that they don't seem to be dynamic in the way I was hoping. For example, in the sheet you uploaded, the columns are still specifically referring to a particular name. I would like it better if I could refer to the column header (the date in C2 for example) instead of using the static reference of FINAL[5-Jun]. Same thing for the Meal reference.

I'm not sure if that makes any sense, so I am including a picture of the thoughts running in my mind.
Picture1.png

Any ideas on how to do that?

That's kind of what I was affraid of.

Thanks John! I'm going to call this one Solved - in as much as I have the answers I need for my current task...even if it isn't exactly what I had hoped for.

See attached:

in C3

=SUMPRODUCT((Final!$D$4:$D$150)*(Final!$A$4:$A$150=$A3)*(INDEX(Final!$J$4:$L$150,,MATCH($B3,Final!$J$3:$L$3,0))<>"---")*(Final!$U$3:$CC$3=TEXT(C$2,"d-mmm"))*(Final!$U$4:$CC$150="Y"))

copy down to row 5 and copy across

in C6

=SUMPRODUCT((Final!$D$4:$D$150)*(Final!$A$4:$A$150=$A3)*(Final!$U$3:$CC$3=TEXT(C$2,"d-mmm"))*(Final!$U$4:$CC$150="Y"))