How to sum with index match formula in MS excel

Question

I would like to sum the data in column F (Sheet1) and show the result in column B (Sheet1)(From B4 to B9). But the sum range should be created using vlookup or index/match.

The column E in sheet1 matched with Column B in sheet2 and take the column A values in sheet2. Then sum the data in column F (Sheet1)

=SUMIF(E4:E13;VLOOKUP(A4;Sheet2!A17:B30;2;FALSE);F4:F13)
=SUMIF(E4:E13;INDEX(A17:A30;MATCH(A4;Sheet2!B17:B30;0);1);F4:F13)

I used the formula above but its not working. Vlookup takes only first value And index macth showing wrong number.

Can anyone help me? Thanks

To be honest, easiest way would be a VLOOKUP to bring data values into the series section (at bottom) and then a SUMIF in the top part to get your calculation donde. It's two steps but easy formulas. Not sure if this can be achieved with a single one. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jul 11, 2022 at 13:20
SUMIF is a nightmare to audit because one cannot see what makes up the table. Pivot Tables have the advantage that when one gets unexpected results, one can double click on a value to see what makes it up. Admittedly one has to remember to refresh Pivot Tables. — zsalya
– zsalya, Commented Jul 11, 2022 at 15:12

Tom Sharpe · Accepted Answer · 2022-07-11 18:40:57Z

2

Plz try this starting in B4:

=SUMPRODUCT(F$4:F$13*COUNTIFS(A$17:A$30;A4;B$17:B$30;E$4:E$13))

(I am testing this in Excel 365 - it may need array entering in Excel 2010).

Assumes there are no duplicates in the Sheet2 data - if there were, you would need:

=SUMPRODUCT(F$4:F$13*(COUNTIFS(A$17:A$30;A4;B$17:B$30;E$4:E$13)>0))

edited Jul 11, 2022 at 18:40

answered Jul 11, 2022 at 17:28

Tom Sharpe

35.2k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Foxfire And Burns And Burns · Accepted Answer · 2022-07-11 13:28:31Z

1

In case a 2 steps solution may work for you, you can do:

Formula in cell C17 is =IFERROR(VLOOKUP(B17;$E$4:$F$13;2;FALSE);0)
Formula in cell B4 is =SUMIF($C$17:$C$30;$A$17:$A$30;A4)

Drag down both formulas

answered Jul 11, 2022 at 13:28

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

1 Comment

Deepak Over a year ago

Thanks. But i would like to finish in a single formula

Collectives™ on Stack Overflow

How to sum with index match formula in MS excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related