Skip to content

VLOOKUP | Sum ALL Matching Values Or Sum ENTIRE Matching Row or Column

    This video will show how to achieve two things in the context of using Lookup functions such as VLOOKUP, LOOKUP, XLOOKUP, INDEX & MATCH.  You can download the featured files here.

    XLOOKUP Version – requires Office 365 or Excel Online.

    Non XLOOKUP Version – works in any version of Excel.

    Sum all matching values

    Here we want to calculate the total cost of our shopping list.  The total comes to £124 – but how do you calculate this in a single cell?  We will look at how to do this with XLOOKUP and then LOOKUP with SUMPRODUCT.

    The XLOOKUP version of the formula is =SUM(XLOOKUP(E2:E5,A2:A24,B2:B24))

    The LOOKUP version of the formula is =SUMPRODUCT(LOOKUP(E2:E5,A2:B24))

    Sum all values contained in the matching row or column

    Here we want to add up the sales in the matching column.  The video shows you how to do this with XLOOKUP and then with HLOOKUP & SUMPRODUCT and then with INDEX, MATCH & SUM.

    The XLOOKUP version of the formula is =SUM(XLOOKUP(I11,J2:S2,J3:S8))

    The HLOOKUP version of the formula is =SUMPRODUCT(HLOOKUP(I11,J2:S8,{2,3,4,5,6,7},0))

    The INDEX version of the formula is =SUM(INDEX(J3:S8,,MATCH(I11,J2:S2,0)))