» Summarize data according to criteria from the data table using Sumif formula, Offset formula and Combo Box in Microsoft Excel
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.
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
Step 2: The SUMIF formula
Select column B, press Ctrl+F3, enter ColB in the Names in worksheet box, and then click OK.
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.
Step 3: The OFFSET formula
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.
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.
Step 4: Combining the OFFSET and SUMIF formulas
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.
In the combined formula below, the data from January 2001 is summarized according to a criteria.
Step 5: Combo box (read more how to add Combo Box)
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.