2

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

Sumif

Deepak
  • 473
  • 1
  • 10
  • 32
  • 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 Jul 11 '22 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 Jul 11 '22 at 15:12

2 Answers2

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

enter image description here

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))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
1

enter image description here

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

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

Drag down both formulas

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38