Hello,



I hope I can explain my question - I try to solve this problem for a few days and didn't find any solution.

I attache the file to make it easier to understand, short explanation of attached file, it have 3 pages:
1. Spots - it contains 2 tables. First table is connected to 2nd one. For example "L1" = "20-35 U"
2. PRIME - starting on row 7 it contains a table with Months, Weeks, Days and hours. In this table will be added and frequently changed "spots". Bellow, on row 86 starts a small table - this one I can't complete!3. AMR% - this page contains an extract of data from a program, number of rows till word "Channel" (B31) can change, also number of columns can change, but number of hours never change, only their position could change, because number of rows till word "Channel" (B31) can change. The important part here is that each column have 72 random numbers for each hour on weekdays and weekends and each column have 2 names - channel and TA. For example "Prime" and "20-35 U". This is important for our formula as we will extract data from this table.

Question:
I need to complete table on page "PRIME" starting on row 86, there should be sum of numbers from page AMR% and meet all criteria. For example on 18.03.2017 in column I on row 88 result will be 5,9 which is a sum of "YG1" in cell (I76) and "YG2" in cell (I60). Both have their criteria: YG1 - "PRIME" - "20-35 U" - weekend - 19:30 and YG2 - "PRIME" - "36-50 U" - weekend - 11:30. According to this criteria it sums all from page AMR%.

Because data in table from row 7 to 84 will change frequently I need a general formula that will take in account all criteria from page "spots", look at what hour is located each spot and then sum only required cells from page AMR%.


Did my best to explain all of this, if you have any questions, don't hesitate to ask. But sometimes I might not have the chance to answer fast, please don't close the question fast.

Attached Files

excel puzzle.xlsx (158.2 KB, 11 views)

I don't understand the value in I88 (='AMR%'!W94+'AMR%'!X80). Why do yoi add W94 when PRIME 20-35U weekend 19:30 is W96?

If you want formulas in PRIME!D86:CW95 then please give some more examples of expected results (at least 5) so it is possible to make some testing. Just one expected result is not very helpful.

Yes, you're right - there should be W96, sorry for that mistake.
Also added more examples, if anything else - write and I will respond asap.

Attached Files

excel puzzle.xlsx (158.4 KB, 11 views)

OK, I will take a look at it, but do not expect anything from me today.

@Kradores

I regret, but I give up this one.
Maybe you should start a new thread and hope someone else will accept the challenge.