I have the below formula.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$B$3:$B$100"),$B3,INDIRECT("'"&Sheet1!$A$2:$A$4&"'!d$3:d$100")))/SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$B$3:$B$100"),$B3,INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$p$3:$p$100")))
I need to autofill across columns where the d$3:d$100 updates with the column. I'm calculating weighted averages for different companies between 3 spreadsheets for a 12 month calendar year. I know I could just update the d's to the appropriate column letter but feel there has to be an easier way.
How can I write this so it would work?
Travis
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$B$3:$B$100"),$B3,INDIRECT("'"&Sheet1!$A$2:$A$4&"'!d$3:d$100")))/SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$B$3:$B$100"),$B3,INDIRECT("'"&Sheet1!$A$2:$A$4&"'!$p$3:$p$100")))
I need to autofill across columns where the d$3:d$100 updates with the column. I'm calculating weighted averages for different companies between 3 spreadsheets for a 12 month calendar year. I know I could just update the d's to the appropriate column letter but feel there has to be an easier way.
How can I write this so it would work?
Travis
