|  

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

CATEGORY - Summing VERSION - All Microsoft Excel Versions
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)


Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation