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
CodePudding user response：
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
CodePudding user response：
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))