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))
If the arrangement is such where the values are always in the same spot in each sheet, you can use a 3D reference.
=SUM(Sales:Referrals!B2)
If the values are not in the same place in each sheet then an INDEX MATCH would not work.
Hmm, the 3D reference here would not work as the place it is sum'ing the values differs on each sheet. Patrick2788
