Forum Discussion
Solved
INDEX & MATCH with SUMPRODUCT across multiple sheets
Hello All, I've provided a brief sample. I need the formula to pull based on month in my dynamic drop down list the combined product of "Sales" and "Referrals" into "Totals" for the correlating c...
That's as
=INDEX(Sales!$A$1:$M$11,MATCH($A2,Sales!$A:$A,0),MATCH(B$1,Sales!$1:$1,0)) + INDEX(Referrals!$A$1:$M$11,MATCH($A2,Referrals!$A:$A,0),MATCH(B$1,Referrals!$1:$1,0))
What would that formula look like? SergeiBaklan
That's as
=INDEX(Sales!$A$1:$M$11,MATCH($A2,Sales!$A:$A,0),MATCH(B$1,Sales!$1:$1,0)) +
INDEX(Referrals!$A$1:$M$11,MATCH($A2,Referrals!$A:$A,0),MATCH(B$1,Referrals!$1:$1,0))
I'm glad you received much help since my previous post. Allow me to help you out now.
You are trying to index the a third row element (from E2:E4) in a range with only one row (H4:S4). Change the first one to E4:E4 or the second one to H2:H4 and it will work. But....... why not try to make it all be bit more dynamic AND easier to follow and maintain. Re-attaching your workbook with your formula re-written, using Named Ranges as I assume you want to be able to copy the formula down to cover more than one "Measured By".
I can't get it to work in the actual sheet... here is an abbreviated version. Can you take a look? SergeiBaklan
