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.

## Leave a Comment