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.
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.
