When we need to summarize the values in a dataset based on matching instances with single criteria then we use the SUMIF function. We can also nest the VLOOKUP function inside of the SUMIF function in order to make a dynamic SUMIF function. This tutorial will step through the process.
Figure 1. Combining the SUMIF and VLOOKUP Functions
The SUMIF and VLOOKUP Functions
Suppose we have a dataset of vendors’ invoices in a table range A2:C12 where invoice numbers and amounts are listed with respect to vendors’ IDs. But we want to sum the amounts for a given “Vendor Name” as criteria. As our table range A2: C12 does not contain “Vendor Name” field, so we are unable to sum the invoices amounts using simply the SUMIF function.
Figure 2. The SUMIF Function Failing Result
Looking Up the Criteria Value
In this case, we need to look up for the criteria value (Vendor ID) associated with the given value (Vendor Name). As each vendor has a unique vendor ID, let say listed in a table range H2:I9, so we can look up for the vendor ID associated with given vendor name using the VLOOKUP function in criteria argument of the SUMIF function as follows:
=SUMIF($A$2:$A$12,VLOOKUP($E$2,$H$2:$I$9,2,FALSE),$C$2:$C$12)
In this formula, the VLOOKUP function returns the Vendor ID as criteria argument value associated with Vendor Name and resultantly the SUMIF function summarizes the invoices’ amounts matching the criteria value.
Figure 3. The Output of SUMIF and VLOOKUP Functions
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.
Leave a Comment