Sum All Matches with VLOOKUP in Excel (3 Easy Ways)

In this article, I shall show you how you can sum all matches with VLOOKUP in Excel. You’ll learn how to sum all the matches using the VLOOKUP function, along with the uses of the FILTER function and the IF function in combination with the SUM function.


Download Practice Workbook


3 Ways to Sum All Matches with VLOOKUP in Excel

Here we’ve got a data set with the Names, Authors, and Prices of some books of a bookshop called Martin Bookstore.

Data Set to Sum All Matches with VLOOKUP in Excel

Let’s try to find out the sum of all matches with VLOOKUP from this data set.


1. Use FILTER Function to Sum All Matches with VLOOKUP in Excel (For Newer Versions of Excel)

Those who have access to an Office 365 account, can use the FILTER Function of Excel to sum all matches from any data set.

In the given data set, the formula to find out the sum of the prices of all the books of Charles Dickens will be:

=SUM(FILTER(D4:D13,C4:C13=F4))

FILTER Function to Sum All Matches with VLOOKUP in Excel

Explanation of the Formula:

  • The FILTER function matches a lookup value with all the values of a lookup column and returns the corresponding values from another column.
  • Here F4 (Charles Dickens) is our lookup value, C4:C13 (Author) is the lookup column, and D4:D13 (Price) is the other column.
  • FILTER(D4:D13,C4:C13=F4) matches all values of the column C4:C13 (Author) with F4 (Charles Dickens) and returns the corresponding values from the column D4:D13 (Price).
  • Finally, SUM(FILTER(D4:D13,C4:C13=F4)) returns the sum of all the prices of all the books returned by the FILTER function.
  • You can change the lookup value to any other author except Charles Dickens in cell F4, and it will return the total price of the books of that author.

Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)


2. Use IF Function to Sum All Matches with VLOOKUP in Excel (For Older Versions of Excel)

If you use an older version of Excel, you can use the IF function of Excel to sum all the matches from any data set.

The sum of the prices of all the books of Charles Dickens can be found using this formula:

=SUM(IF(C4:C13=F4,D4:D13,""))

[It is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

Explanation of the Formula:

  • IF(C4:C13=F4,D4:D13,””) matches all values of the lookup column C4:C13 (Author) with the lookup value F4 (Charles Dickens).
  • If the lookup value F4 matches the lookup column C4:C13 (Author), then it returns the corresponding value from the column D4:D13 (Price).
  • And if it doesn’t match, it returns a blank string “”.
  • Finally, SUM(IF(C4:C13=F4,D4:D13,””)) returns the sum of all the values returned by the IF function.

Read More: How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)


Similar Readings


3. Use VLOOKUP Function to Sum All Matches with VLOOKUP in Excel (For Older Versions of Excel)

You can also use the VLOOKUP function of Excel to sum all the values that match the lookup value.

Step 1:

➤ Select the adjacent column left to the data set and enter this formula in the first cell:

=C4&COUNTIF($C$4:C4,C4)

⧪ Note:

  • Here C4 is the first cell of the lookup array (Author). You use the one from your data set.

COUNTIF Function to Sum All Matches with VLOOKUP in Excel

Step 2:

➤ Drag the Fill Handle up to the last cell.

➤ It will create a sequence of the authors along with the ranks. Like Charles Dickens1, Charles Dickens2, Elif Shafak1, Elif Shafak2 and so on.

[The Ampersand Symbol (&) concatenates two strings].

Dragging the Fill Handle to Sum All Matches with VLOOKUP in Excel

Step 3:

➤ Enter the lookup value in a new cell.

➤ Here I have entered Charles Dickens in cell F4.

Step 4:

➤ Finally, enter this formula in another cell:

=SUM(VLOOKUP(F4&ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,F4))),A4:D13,4,FALSE))

[It is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

VLOOKUP Function to Sum All Matches with VLOOKUP in Excel

Look, it returns the sum of the prices of all the books of Charles Dickens, $52.00.

Explanation of the Formula:

  • COUNTIF(C4:C13,F4) returns 3, as there are a total of 3 cells in the range C4:C13 (Author) that contain the lookup value F4 (Charles Dickens). See the COUNTIF function for details.
  • A1:INDIRECT(“A”&COUNTIF(C4:C13,F4)) now becomes A1:A3. See the INDIRECT function for details.
  • ROW(A1:INDIRECT(“A”&COUNTIF(C4:C13,F4))) becomes ROW(A1:A3) and returns an array {1, 2, 3}. See the ROW function for details.
  • F4&ROW(A1:INDIRECT(“A”&COUNTIF(C4:C13,F4))) becomes F4&{1, 2, 3} and returns an array {Charles Dickens1, Charles Dickens2, Charles Dickens3}.
[The Ampersand Symbol (&) concatenates two strings].
  • VLOOKUP(F4&ROW(A1:INDIRECT(“A”&COUNTIF(C4:C13,F4))),A4:D13,4,FALSE) now becomes VLOOKUP({Charles Dickens1, Charles Dickens2, Charles Dickens3},A4:D13,4,FALSE).
  • The VLOOKUP function matches the lookup value with all the values of the first column of the data set and then returns the corresponding values from another column.
  • Here the lookup value is the array {Charles Dickens1, Charles Dickens2, Charles Dickens3}.
  • Therefore it matches the lookup values with all the values of the first column A4:A13, and returns the corresponding values from the 4th column (Price).
  • Finally, the SUM function returns the sum of all the prices that match the lookup values.

Read More: How to Use VLOOKUP with SUM Function in Excel (6 Methods)


Conclusion

Using these methods, you can sum all matches with VLOOKUP in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Hello,
    So I’m trying to create a sum of two separate columns with the vlook from two different columns equals a given name.
    What I have is an excel spreadsheet that downloads from an external platform. The scenario is:
    If column “K” is blank then I need the sum of column “N” for any rows with the label “asphalt field” in column “I” and if column “K” has a matching label then I need the sum of columns “j” & “L” that match the label?

    I have been trying various different formula setups but this is way above my ability. please help!

    • Hi KEITH,

      Your problem is partly vague I think. Still, I’ve tried to build a formula that might work for you. If this doesn’t work, I would recommend you share your workbook with me or at least share a sneak peek of your dataset.

      Now use this formula:

      =IF(ISBLANK(K2),SUMIF(I2:I13,”asphalt field”,N2:N13),SUM(J2:J13,L2:L13))




      sum based on conditions




      Thanks!

Leave a reply

ExcelDemy
Logo