Tip Printed from ExcelTip.com

---

Summarize data according to criteria from the data table using Sumif formula, Offset formula and Combo Box in Microsoft Excel


The SUMIF formula summarizes data according to criteria. The OFFSET formula enables us to change the reference. The combination of the two formulas and the addition of a Combo Box allow you to easily summarize data according to criteria from the data table.

Example
Task: Summarize the Profit and Loss by P&L items (see P&L screen shot, column B) for the month of January 2001 or any other month that you choose.

Step 1: Specify a name for column B in the worksheet.

• Select column B, press Ctrl+F3, enter ColB in the Names in worksheet box, and then click OK.

Step 2: The SUMIF formula

• The SUMIF formula summarizes data according to criteria.
• The formula has 3 arguments:

• First argument – Range – ColB (column B in the data table sheet).
• Second argument – Criteria – b3 (the text: Revenue).
• Third argument – Sum_range – D:D (column D, January 2001). This is the data range from which the data-by-criteria will be summarized.

Problem

• The SUMIF formula is excellent for summarizing data according to criteria. However, there is a limitation: you cannot make any changes in the summary range when you use the SUMIF formula. In the example above, you summed up the data from the January 2001 column. How, then, can you easily change the range of the sum in order to sum from the column of March or April (instead of from January)?
• There is a third argument in the VLookup formula: changing the column number also changes the number of the intersected column.
• The Index formula is much more flexible; you can change both the number of the row and the number of the column.
• The SUMIF formula needs help, and the solution is to add the OFFSET formula.

Step 3: The OFFSET formula

The OFFSET formula returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
The formula in the example returns a value from a cell that is a given distance from the base cell of B4. The distance is 0 rows, 2 columns. The cell address is D4.

Step 4: Combining the OFFSET and SUMIF formulas

In the combined formula below, the data from January 2001 is summarized according to a criteria.
The formula is =SUMIF(ColB,B3,OFFSET (ColB,0,2))
Changing the third argument in the OFFSET formula will offset (reposition) the data summary range. In order to accomplish this, add a Combo Box to the sheet.

Step 5: Combo box (read more how to add Combo Box)

Add a Combo Box. The name of the cell linked to the combo box is MonthNumber. In the screen shot, see the third argument of the SUMIF formula in the formula bar. The name of the cell that is linked to the Combo Box appears here.
Combining SUMIF, OFFSET and a Combo box provides an incredibly powerful tool for querying and summing data from a report.