index match multiple sheets and then sum the value

[AKuma0411]

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 forward to some solution! thanks!

[#2]

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

[#3]

This formula is great and is working as per my requirement. I made slight tweak to this formula though. Here's what I want to do:
=SUM(IFERROR(FILTER(UEPL_Final!$N$6:$N$200, UEPL_Final!$E$6:$E$200 = $C11,LEPL_Final!$N$6:$N$200, LEPL_Final!$E$6:$E$200 = $C11,IMN_Final!$N$6:$N$200, IMN_Final!$E$6:$E$200 = $C11), 0))
when I use this it gives me prompt "too many arguments"
Can you please help me resolve/fix this logic?
Thanks!

[#4 · Accepted solution]

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

[#5]

@NikolinoDE 

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

[#6]

this is super awesome and worked perfectly for my requirement! thanks!