EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP with Sum
Secondary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

VLOOKUP with Sum

By Ashwani JaiswalAshwani Jaiswal

VLOOKUP with Sum in Excel

VLOOKUP with Sum in Excel (Table of Contents)

  • VLOOKUP with Sum in Excel
  • Vlookup in Excel Examples

VLOOKUP with Sum in Excel

Vlookup with Sum function in Excel is used to Sum the numbers from the looked up range if the selected range matches the lookup value. We can choose multiple columns from the selected table from where we want to Sum the values. For example, we have a table with sales data of fruits with the sales of different months in different columns. Then using Vlookup with Sum function will return the sum of any selected lookup cell containing the fruit name, which will sum up the numbers from all the selected columns.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Vlookup in Excel Examples

Let’s look at some examples for vlookup with a sum in excel.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)
You can download this VLOOKUP with Sum Excel Template here – VLOOKUP with Sum Excel Template

Vlookup with Sum in Excel – Example #1

We have sales data of some products named as A, B, C,….H of 8 months. Where we need to see the total sale of any product in one shot. The data is shown below.

Example 1-1

For that, we will be using Vlookup with Sum. First, we need to identify the criteria, which we want to see as output. Here I am considering to see the output in a cell below in the same table. For that, go to the edit mode in any of the cells where the output will be printed. Now paste the formula =SUM(VLOOKUP(A2,’Sale Data’!$A$2:$I$9,{2,3,4,5,6,7,8,9},FALSE)) in edited cell.

And press Ctrl + Shift + Enter to see the output. The below screenshot shows the outcome of the applied formula.

VLOOKUP with Sum example 1-2

Let’s do some experiments. For that, instead of Ctrl + Shift + Enter, press only Enter to exit from the cell. As we can see in the below screenshot, it will not give the summed output of the entire selected row by just pressing the Enter key. Only the first value of that row will get reflected in this process.

VLOOKUP with Sum example 1-3

This simple logic can summarize the formula which is given above.

=SUM(VLOOKUP(Lookup Value, Lookup Range, {2,3,4…}, FALSE))

  • Lookup value is the fixed cell, for which we want to see sum.
  • Lookup Range is the complete range or area of the data table from where we want to look up the value. (Always fix the Lookup range so that for other lookup value, the output will not get disturbed)
  • {2,3,4… } are the column numbers, for which we need to see the sum of lookup cell.
  • FALSE is the condition, which says we need to see exact results; we can use TRUE as well, which is used to see the nearly approximate result. This can be used when our data is in discrete form.

This is the main feature, which is why Microsoft enabled the actual output to be seen only by pressing Ctrl + Shift + Enter keys. It shows that when we correctly follow all the steps, only we will see the actual output.

Vlookup with Sum in Excel – Example #2

Again, we will consider the same data as shown in the below screenshot and see the products’ output in one shot.

Example 2-1

For this, identify a worksheet or place it in the same sheet, where the output needs to be seen. I have chosen a separate sheet in the same file to see the output. And put the same logical formula as we have seen above as =SUM(VLOOKUP(A2,’Sales Data’!$A$2:$I$9,{2,3,4,5,6,7,8,9},FALSE)) and press Ctrl + Shift + Enter. We will get the output as below screenshot.

VLOOKUP with Sum example 2-2

As we can see, for all the product their summed sales value for 8 months are here. You can cross-check as well if you want to compare the results.

Let’s do one more experiment and compare; what will be the difference in output if we press Ctrl + Shift + Enter and only Enter key.

VLOOKUP with Sum example 2-3

Here we have two output sets. And we can compare the data as well. By pressing the only Enter key, the formula is printing only the first column data of the sales table, which is in column C. And by pressing Ctrl + Shift + Enter keys simultaneously, we are getting actually some of the sales of subsequent products.

Pros of Vlookup with Sum

  • Once the formula is applied, even if we change the worksheet or excel file, the value will be the same, and the results will not differ.
  • Even if we have to press Ctrl + Shift + Enter to see the exact result, but the outcome will get frozen, and it will allow us to have exact results.

Cons of Vlookup with Sum

  • We need to select all the columns one by one by entering the column numbers in sequence separated by commas after lookup range, instead of selecting all the columns at the time, which we generally do in simple Vlookup formula.

Things to Remember about the Vlookup with Sum in Excel

  • Always freeze the lookup range by pressing the F4 key, which will put a $ sign on both sides. Which indicates that the cell or row or range is fixed. And the value will not get changed, even if we change the range or sheet or file location.
  • Please keep track of columns that need to be considered in summing the count; sometimes, we may miss any number in the following.
  • Once we exit from the cell, the Vlookup with Sum formula itself gets enclosed in curve brackets {}, but if we wish to remove it, it will again get hidden in the same cell and appear again when we exit from it. This is the default function in Excel, which saves this formula.
  • Always format the content before applying Vlookup with Sum; by doing this, it will remove unwanted characters, spaces, and special characters as well to get the filtered sum value.

Recommended Articles

This has been a guide to VLOOKUP with Sum in Excel. VLOOKUP with Sum in Excel is a feature that automatically adjusts the width or height of a cell. Here we discuss how to use VLOOKUP with Sum in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Tutorial in Excel
  2. VLOOKUP Function in Excel
  3. VBA VLOOKUP Function
  4. VLOOKUP Examples in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download VLOOKUP with Sum Excel Template

EDUCBA

Download VLOOKUP with Sum Excel Template

EDUCBA

डाउनलोड VLOOKUP with Sum Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more