Summing Columns on a variable Excel Doc

Hey! I've been trying to use a combo of SUM, INDIRECT, and MATCH formulas, but can't figure this out. Help would be appreciated =)

I have one Excel Spreadsheet (StatsCollector) that I'm using to grab stats from multiple other docs, labeled "watch_time._______"

excel1.PNG

I want to be able to put the documents name into column A, and then have all the other columns fill in with the appropriate data from the referenced watch_time doc. I couldn't find a way to reference a variable closed Excel doc by using the file location, so if all the other watch_time docs need to be open when referencing, that is fine.


This is the formula currently in the "Views" Column - which works perfectly when I have the appropriate watch_time doc open:
=SUM(INDIRECT("'["&A2&".xls"&"]"&"Date"&"'!"&"C:C"))

**NOTE: A2 = the name of the file, "Date" = tab name

However, I want to change this to specifically search and sum the "Views" column in the watch_time doc, rather than a fixed C:C - as the people making the watch.time docs sometimes re-arrange or mess them up.



This is the formula I was able to use to reference a specific cell:
=INDEX(INDIRECT("'["&A2&".xls"&"]"&"Date"&"'!"&"A1:BA200"),MATCH("2016-11-22",INDIRECT("'["&A2&".xls"&"]"&"Date"&"'!"&"A1:A200"),0),MATCH("Watch time (minutes)",INDIRECT("'["&A2&".xls"&"]"&"Date"&"'!"&"A1:BA1"),0))

**NOTE: A2 = the name of the file, "Date" = tab name

In this example it's indexing row "2016-11-22" and column "Watch time", but I don't know how to change it to a sum function for all rows in the "Watch Time" column

excel2.PNG

___________________________________________________________________________________________________
I feel like I got the right elements, I just can't figure out how to combine them. The indirect functions boggle my brain.