Sumproduct Count Dates by Week Number- Indirect Sheet Name & Header

[mdonovan890]

I am working within Excel 2016 and I am currently using the following formula to count the number of dates equal to the week number. The formula uses indirect to obtain the sheet name in cell $J1 and counting the dates listed in column C.



The formula works well but I am wondering if there is a way to incorporate matching the header name?

Meaning, instead of counting the dates in column C, finding the column on the sheet that has “process date” in the header and then counting the dates.

Current Formula:

IF(J$1<>"",SUMPRODUCT(--(ISOWEEKNUM($C7)=ISOWEEKNUM(INDIRECT("'"&J$1&"'!$C$3:$C$8000"))),--(YEAR($C7)=YEAR(INDIRECT("'"&J$1&"'!$C$3:$C$8000")))),"")



My Attempt: K1 has the header name

=IF(J$1<>"",SUMPRODUCT(--(ISOWEEKNUM($C2)=ISOWEEKNUM(INDIRECT("'"&J$1&"'!$a$1:$z$8000"),MATCH(K1,INDIRECT("'"&$J$1&"'!$a$1:$z$1")),--(YEAR($C2)=YEAR(INDIRECT("'"&J$1&"'!$a$1:$z$8000")),MATCH(K1,INDIRECT("'"&$J$1&"'!$a$1:$z$1"),0),0))),"")

 

[Fluff]

Maybe

=IF(J$1<>"",SUMPRODUCT(--(ISOWEEKNUM($C2)=ISOWEEKNUM(INDEX(INDIRECT("'"&J$1&"'!$a$1:$z$8000"),0,MATCH(K1,INDIRECT("'"&$J$1&"'!$a$1:$z$1"),0)))),--(YEAR($C2)=YEAR(INDEX(INDIRECT("'"&J$1&"'!$a$1:$z$8000"),0,MATCH(K1,INDIRECT("'"&$J$1&"'!$a$1:$z$1"),0))))),"")