Spreadsheets: Wrangling a VLOOKUP

Is it possible to sum all of the matching records from a VLOOKUP formula?


Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.

Reader Cassi M. wins a copy of the book Guerilla Data Analysis Using Microsoft Excel for her question: “Is it possible to sum all of the matching records from a VLOOKUP formula? Can you do =SUM(VLOOKUP(“Jan”,Range)), =SUM(VLOOKUP(“Feb”,Range)) and so on?” Unfortunately, VLOOKUP will only return the first matching value from a table. So when you sum the result of the VLOOKUP, you will get only the first record from January, which in this case would return $46,699.31.

I love the thought process behind the formula shown in Figure 1 (below).

The solution is to turn to the SUMIF function. The SUMIF function can look through a range such as B2:B24, looking for all values that match “Jan.” Excel will then add up all the corresponding matching cells from a sum range such as C2:C24.

A typical formula might be =SUMIF($B$2:$B$24,”Jan”,$C$2:$C$24).

 

Fig. 1

MrExcel07Fig1

 

To make the formula more flexible, move the “Jan” to another cell. Add dollar signs around the range and sum range to make those references absolute. In Figure 2 (below), the formula of =SUMIF($B$2:$B$24,E2,$C$2:$C$24) is copied down to create the total receipts for each month.

Fig. 2

MrExcel07Fig 2

 

The SUMIF and COUNTIF functions have been in Excel since 1997. AVERAGEIF was not added until Excel 2007. Also in Excel 2007, Microsoft added new plural versions of these three functions. The Excel 2007 function of SUMIFS can add up records that match two or more criteria.

Bill Jelen is the author of 32 books, including Special Edition Using Excel 2007, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.

Discuss

Your email address will not be published. Required fields are marked *