SUMIF in INDEX function

[lemonap618]

Hello All,
I have a quick question. I'm trying to write a formula that will use the values in both cells A7 (employee name) and P6 (date) to be used as the criteria in a sumif function. The range is where I'm struggling as I would like the formula to look at all the individual worksheets in the workbook. Is that possible? Finally, the sum range would be in a different column in each workbook, so the sum range would have to look for the "date" column header. I hope I'm making sense here.
Any and all help is appreciated!

Regards,
Andrew

 

[lemonap618]

Hi All,
I think I was being confusing hahaha.
So I strung together vlookup statements which got me the answer; however, when I go to add another month of data, I will have to shift the vlookup to the next column. I am looking for a more intuitive way to present the following:

=IFERROR(VLOOKUP(A7,SE_EL_2019_agile!A:F,6,0),IFERROR(VLOOKUP(A7,SE_agile_Mercury1!A:F,6,0),IFERROR(VLOOKUP(A7,SE_PCB_2019!A:F,6,0),IFERROR(VLOOKUP(A7,SE_PCB_2019_agile!A:F,6,0),IFERROR(VLOOKUP(A7,SE_Cloud1!A:F,6,0),IFERROR(VLOOKUP(A7,SE_agile_Cloud1!A:F,6,0),IFERROR(VLOOKUP(A7,SE_Skylight1!A:F,6,0),IFERROR(VLOOKUP(A7,SE_agile_Skylight1!A:F,6,0),0))))))))