Search code examples
excelexcel-formulaexcel-2010vlookupsumifs

How to sum with index match formula in MS excel

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

Solution

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