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 With VLOOKUP (Table of Contents)
SUMIF With VLOOKUP
SUMIF is a function 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 is used with VLOOKUP function 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.
This article demonstrates the combined use of SUMIF and VLOOKUP to get the desired values by focusing on the following topics.
Explanation of SUMIF with VLOOKUP
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
- Range: It is the range of the cells used to assess the established criteria
- Criteria: It is the condition to sum the values. It may be a cell reference, number, and another excel function. When we want to combine SUMIF with VLOOKUP, the vlookup function will be entered in the place of criteria
- Sum Range: It is the range of cells specified to sum the numerical values.
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 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?
The combined use of SUMIF and VLOOKUP functions 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.
Second Method: SUMIF function can be inserted from the ‘Formula’ Tab as shown in the image.
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 of SUMIF with VLOOKUP
Below are the examples of Sumif with Vlookup in excel.
Example #1 – Use of SUMIF and VLOOKUP together To Determine Sum Value
This example shows how to use the SUMIF and VLOOKUP Functions together to find the sum of the sales in the same month in different years. The following data is considered for this example as shown in the screenshot.
The lookup table considered is shown as mentioned below. It included reference values for the months from January to December.
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 and VLOOKUP function is shown as,
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.
The main table data is shown the below screenshot.
To determine the total sales, the Name of the salesperson is taken as lookup value and employee ID’s are used for reference purpose. The formula is entered as presented in the below and few changes are observed to the formula when compared to the first example.
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.
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 of Sumif with Vlookup
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 meet the criteria mentioned in different aspects of a business.
Things to Remember
The following tips should be followed by a user to use SUMIF with VLOOKUP properly.
- While using the VLOOKUP function, the column index number should not be lower than 1 to avoid errors.
- Indexes should be given to the lookup table columns by indicating with numbers 1, 2, 3, and so on.
- CTRL+SHIFT+ENTER should be used instead of entering Key since VLOOKUP is entered as an array formula.
- Defining two tables including Main and Lookup is required to extract values and determine the sum of the array values.
- SUMIF provides accurate results for only numerical data, it won’t work for another kind of data.
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 downloadable excel template. You may learn more about excel from the following articles –