Forum Discussion
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 cell.
i.e. cell B2 in "Totals" should be the SUM of B2 in "Sales" and B2 in "Referrals" (based on the month selection from the dynamic dropdown list)
What in SAM HILL TARNATION is going on?!?!
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))
11 Replies
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
What "dynamic drop down list"? There are no formulae in your sheet other than the ones that generate random numbers in "Sales" and "Referrals". Why not just add the following in B2 in "Totals"?
=Sales!B2+Referrals!B2..... and drag it down and across. Otherwise, please clarify and indicate which value you would want to see where and why.
Hello and thank you for your response.
As this is an example i can only explain what i'm trying to do. My actual worksheet has 5 or 6 tabs and one main tab in which there are various items where the total is needed for different segments based on the month selected in the list. That total is drawn from combining the correlating segments from the other tabs all into one cell in the main dashboard tab.Riny_van_Eekelen
My thought is it would be some combinations of an INDEX and MATCH formula with SUMPRODUCT, but i could be wrongshade206 Everybody is right until proven wrong!
But, it's difficult to see what you want without and example that is close(r) to your actual situation. You talk about a drop down list, but it's not in the file you uploaded. Your main tab probably does not look the same as the other ones. So, unless you can upload a more realistic example, I fear that you will not get much help here. Of course, I may be wrong.
