SOLVED
Home

INDEX & MATCH with SUMPRODUCT across multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1099807%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20%22%3CSPAN%3Edynamic%20drop%20down%20list%22%3F%20There%20are%20no%20formulae%20in%20your%20sheet%20other%20than%20the%20ones%20that%20generate%20random%20numbers%20in%20%22Sales%22%20and%20%22Referrals%22.%20Why%20not%20just%20add%20the%20following%20in%20B2%20in%20%22Totals%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSales!B2%2BReferrals!B2%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B.....%20and%20drag%20it%20down%20and%20across.%20Otherwise%2C%20please%20clarify%20and%20indicate%20which%20value%20you%20would%20want%20to%20see%20where%20and%20why.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099834%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099834%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20and%20thank%20you%20for%20your%20response.%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20this%20is%20an%20example%20i%20can%20only%20explain%20what%20i'm%20trying%20to%20do.%20My%20actual%20worksheet%20has%205%20or%206%20tabs%20and%20one%20main%20tab%20in%20which%20there%20are%20various%20items%20where%20the%20total%20is%20needed%20for%20different%20segments%20based%20on%20the%20month%20selected%20in%20the%20list.%20That%20total%20is%20drawn%20from%20combining%20the%20correlating%20segments%20from%20the%20other%20tabs%20all%20into%20one%20cell%20in%20the%20main%20dashboard%20tab.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20thought%20is%20it%20would%20be%20some%20combinations%20of%20an%20INDEX%20and%20MATCH%20formula%20with%20SUMPRODUCT%2C%20but%20i%20could%20be%20wrong%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099891%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3BEverybody%20is%20right%20until%20proven%20wrong!%3C%2FP%3E%3CP%3EBut%2C%20it's%20difficult%20to%20see%20what%20you%20want%20without%20and%20example%20that%20is%20close(r)%20to%20your%20actual%20situation.%20You%20talk%20about%20a%20drop%20down%20list%2C%20but%20it's%20not%20in%20the%20file%20you%20uploaded.%20Your%20main%20tab%20probably%20does%20not%20look%20the%20same%20as%20the%20other%20ones.%20So%2C%20unless%20you%20can%20upload%20a%20more%20realistic%20example%2C%20I%20fear%20that%20you%20will%20not%20get%20much%20help%20here.%20Of%20course%2C%20I%20may%20be%20wrong.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099946%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20arrangement%20is%20such%20where%20the%20values%20are%20always%20in%20the%20same%20spot%20in%20each%20sheet%2C%20you%20can%20use%20a%203D%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(Sales%3AReferrals!B2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20values%20are%20not%20in%20the%20same%20place%20in%20each%20sheet%20then%20an%20INDEX%20MATCH%20would%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099711%22%20slang%3D%22en-US%22%3EINDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099711%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20provided%20a%20brief%20sample.%20I%20need%20the%20formula%20to%20pull%20based%20on%20month%20in%20my%20dynamic%20drop%20down%20list%20the%20combined%20product%20of%20%22Sales%22%20and%20%22Referrals%22%20into%20%22Totals%22%20for%20the%20correlating%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20cell%20B2%20in%20%22Totals%22%20should%20be%20the%20SUM%20of%20B2%20in%20%22Sales%22%20and%20B2%20in%20%22Referrals%22%20(based%20on%20the%20month%20selection%20from%20the%20dynamic%20dropdown%20list)%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20in%20SAM%20HILL%20TARNATION%20is%20going%20on%3F!%3F!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1099711%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100042%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100042%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20absolutely%20right!%20I%20uploaded%20an%20incomplete%20sample%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThat%20has%20now%20been%20corrected%2C%20maybe%20now%20we%20can%20resolve%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100062%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100062%22%20slang%3D%22en-US%22%3E%3CP%3EHmm%2C%20the%203D%20reference%20here%20would%20not%20work%20as%20the%20place%20it%20is%20sum'ing%20the%20values%20differs%20on%20each%20sheet.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100072%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20there%20are%20only%20two%20sheets%20to%20sum%20that%20could%20be%20simply%20(INDEX%2FMATCH%20on%20Sales)%20%2B%20(INDEX%2FMATCH%20on%20Referral)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100082%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100082%22%20slang%3D%22en-US%22%3EWhat%20would%20that%20formula%20look%20like%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100089%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100089%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20would%20that%20formula%20look%20like%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100130%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(Sales!%24A%241%3A%24M%2411%2CMATCH(%24A2%2CSales!%24A%3A%24A%2C0)%2CMATCH(B%241%2CSales!%241%3A%241%2C0))%20%2B%0A%20INDEX(Referrals!%24A%241%3A%24M%2411%2CMATCH(%24A2%2CReferrals!%24A%3A%24A%2C0)%2CMATCH(B%241%2CReferrals!%241%3A%241%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100346%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100346%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20get%20it%20to%20work%20in%20the%20actual%20sheet...%20here%20is%20an%20abbreviated%20version.%20Can%20you%20take%20a%20look%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1100852%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20%26amp%3B%20MATCH%20with%20SUMPRODUCT%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1100852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20glad%20you%20received%20much%20help%20since%20my%20previous%20post.%20Allow%20me%20to%20help%20you%20out%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20trying%20to%20index%20the%20a%20third%20row%20element%20(from%20E2%3AE4)%20in%20a%20range%20with%20only%20one%20row%20(H4%3AS4).%20Change%20the%20first%20one%20to%20E4%3AE4%20%26nbsp%3Bor%20the%20second%20one%20to%20H2%3AH4%20and%20it%20will%20work.%20But.......%20why%20not%20try%20to%20make%20it%20all%20be%20bit%20more%20dynamic%20AND%20easier%20to%20follow%20and%20maintain.%20Re-attaching%20your%20workbook%20with%20your%20formula%20re-written%2C%20using%20Named%20Ranges%20as%20I%20assume%20you%20want%20to%20be%20able%20to%20copy%20the%20formula%20down%20to%20cover%20more%20than%20one%20%22Measured%20By%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
shade206
Contributor

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?!?!

11 Replies
Highlighted

@shade206 

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.

Highlighted

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 wrong

Highlighted

@shade206 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.

Highlighted

@shade206 

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.

Highlighted

You are absolutely right! I uploaded an incomplete sample

That has now been corrected, maybe now we can resolve? @Riny_van_Eekelen 

Highlighted

Hmm, the 3D reference here would not work as the place it is sum'ing the values differs on each sheet. @Patrick2788 

Highlighted

@shade206 

If there are only two sheets to sum that could be simply (INDEX/MATCH on Sales) + (INDEX/MATCH on Referral)

Highlighted

What would that formula look like? @Sergei Baklan 

Highlighted
Solution

@shade206 

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))

 

Highlighted

I can't get it to work in the actual sheet... here is an abbreviated version. Can you take a look? @Sergei Baklan 

Highlighted

@shade206 

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". 

 

Related Conversations
Separate lines on Sheets depending on condition
Mohammed Almaghi in Excel on
1 Replies
Matching Excel cells by column
ddelise in Excel on
3 Replies
How do I group rows in an excel table?
Marisa Medrano in Excel on
1 Replies