SUMIF With VLOOKUP

Combined Use of sumif(vlookup)

Sumif with VLOOKUP is a combination of two different conditional functions, SUMIF is used to sum the cells based on some condition which takes arguments of range which has the data and then the criteria or the condition and cells to sum, instead of the criteria we use VLOOKUP as the criteria when there is a large amount of data available in multiple columns.

SUMIF is a functionSUMIF Is A FunctionThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more presented in Excel from the 2007 version to sum the various values matches to the criteria. VLOOKUP is one of the best formulae to gather data from other tables. When multiple conditions and columns are there, sumif(vlookup) is used to perform the multiple calculations on the excel sheet. The disadvantage of SUMIF function returning the only number is overcome by using VLOOKUP. VLOOKUP helps to return any kind of data from a table based on the matched criteria.

SUMIF With VLOOKUP

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: SUMIF With VLOOKUP (wallstreetmojo.com)

Explanation

SUMIF Function: It is a Trigonometry and Math function to sum the values when the established condition is true. Sum value is obtained based on only one criterion.

When we deal with SUMIF function in excel, the following formula is used

SUMIF Formula

Now, the formula is modified to

Formula = SUMIF (Range, Vlookup (lookup value, table array, column index number, [range lookup]), [sum range])

  • Lookup value: It specifies the value to be searched in a table. It may be a reference or value.
  • Table array: It is the range of the table contained two or more than two columns.
  • Column index number: It is the relative index of a column to specify for returning required data from a specific column.
  • [Range lookup]: It is either 0 or 1 to specify whether the exact value should be returned or the approximate value should be returned. But, it is optional to a user. 0 indicates an exact match and 1 indicates an approximate match.

How to Use SUMIF with VLOOKUP Function?

The combined use of sumif(vlookup) are helpful in searching data based on the single criteria. These are used in many ways in Excel to perform the calculations by searching for data. These are collectively used in the business environment to perform various tasks to make good decisions. To use effectively these functions together,

First, the SUMIF function is to be entered utilizing the two methods.

First Method: Formula needs to type from the keyboard as shown in the below image.

SUMIF Formula

Second Method: SUMIF function can be inserted from the ‘Formula’ Tab as shown in the image.

Sumif with vlookup Formula

After entering the SUMIF function, the formula for VLOOKUP is entered inside of SUMIF function by replacing the ‘Criteria’ element. All the parameters of the VLOOKUP including the lookup value, table array, index number of the column, and range lookup. These are to be enclosed in parenthesis to avoid errors with formula. The range of values to be summed are included in the sum range elements of SUMIF function. At last, CTRL, SHIFT, and ENTER keys pressed together to facilitate the values as an array.

Examples

You can download this SUMIF With VLOOKUP Excel Template here – SUMIF With VLOOKUP Excel Template

Example #1 – Use of sumif(vlookup) together to determine some value

This example shows how to use the sumif(vlookup) together to find the sum of the sales in the same month in different years.

  1. The following data is considered for this example as shown in the screenshot.


    Sumif with Vlookup Example 1

  2. The lookup table considered is shown as mentioned below. It included reference values for the months from January to December.


    Example 1-1

  3. After entering data into the main table and lookup table, SUMIF function is used to determine the total sales generated in different months of a year. Here, the Lookup value considered is the month. The formula combining the sumif(vlookup) is shown as,



    Sumif with Vlookup Example 1-2

    Sumif with Vlookup Example 1-3

The total sales in January month are determined as 17263.3. When we changed look value to another month, the respective total sales are generated.

Example #2 – Determining Sum Based on Matching Criteria in Different Work Sheets

In this example, the lookup table and main table are taken in different sheets rather than a single sheet. The lookup table data is shown in the below screenshot.

Example 2

The main table data is shown in the below screenshot.

Example 2-1

To determine the total sales, the Name of the salesperson is taken as lookup value and employee ID’s are used for reference purposes. The formula is entered as presented below and few changes are observed to the formula when compared to the first example.

Example 2-2

In this example, instead of selecting a lookup array only Lookup_table is mentioned. By pressing the three keys including CTRL, SHIFT, and ENTER accurate results are produced.

Example 2-3

When the name of the salesperson is changed to names mentioned in the lookup, the sum of sales are varied and produces a new result.

Benefits

The following are the benefits of using these functions.

  • We can easily extract the values from another table to perform calculations.
  • Determining the sum of values presented in a range that meets the criteria mentioned in different aspects of a business.

Things to Remember

Recommended Articles

This has been a guide to SUMIF with VLOOKUP in Excel. Here we discuss examples of Sumif with VLOOKUP function to perform the multiple calculations on the excel sheet with a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>