Forum Discussion

AKuma0411's avatar
AKuma0411
Brass Contributor
Dec 05, 2023

index match multiple sheets and then sum the value

Hi,   Is there a formula in excel which can lookup for a match in multiple tabs (around 6) and if it finds a match it should return the value summing up the values where a match was found looking f...
Formulas and Functions
  • djclements's avatar
    djclements
    Dec 07, 2023

    AKuma0411 The FILTER function only accepts three arguments. The basic syntax is:

     

    =FILTER(array, include, [if_empty])

     

    To SUM the results from multiple worksheets, try the following:

     

    =SUM(
   FILTER(UEPL_Final!$N$6:$N$200, UEPL_Final!$E$6:$E$200 = $C11, 0),
   FILTER(LEPL_Final!$N$6:$N$200, LEPL_Final!$E$6:$E$200 = $C11, 0),
   FILTER(IMN_Final!$N$6:$N$200, IMN_Final!$E$6:$E$200 = $C11, 0)
)

     

    If the worksheet tabs are arranged in order from left to right, you could also try using the VSTACK function to reference and filter all three sheets at once. For example:

     

    =SUM(FILTER(VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200), VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200) = $C11, 0))

     

    Or, to improve readability, use the LET function to define the two arrays first:

     

    =LET(
   array1, VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200),
   array2, VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200),
   SUM(FILTER(array1, array2 = $C11, 0))
)

     

    I hope that helps to clear things up. Cheers!

NikolinoDE's avatar
NikolinoDE
Gold Contributor

AKuma0411 

For Excel 365, you can use the new dynamic array functions, specifically SUM, FILTER, and IFERROR, to simplify the formula. Here is an example:

=SUM(IFERROR(FILTER(Sheet1:Sheet6!B:B, Sheet1:Sheet6!A:A = $A2), 0))

This formula takes advantage of the dynamic array functions available in Excel 365. The FILTER function is used to filter values based on the condition, and the SUM function sums up the filtered values.

Enter this formula in a single cell, and Excel 365 will automatically spill the result over adjacent cells if needed. Adjust the cell references and sheet names based on your actual data structure.

Remember that dynamic array functions are specific to Excel 365 and may not work in earlier versions of Excel. AI was partially deployed to support the text.

If this is not what you want or does not work with your version of Excel, I recommend adding more information. Information such as Excel version, operating system, storage medium, etc.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 07, 2023

NikolinoDE 

Unfortunately, FILTER does not support 3D references.  VSTACK does and can be used to stack the data before filtering as djclements has detailed.

Resources