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. **Screenshot // Summarize data according to criteria from the data table using Sumif formula, Offset formula and Combo Box in Microsoft Excel**

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.

I would like to sum values in a column A for all rows where the value in column B = 'X' AND the value in column C = 'Y' I don't seem able to use SUMIF when I have 2 criteria that need to be meet for summing as opposed to just one. Can anyone help please? Thanks

Depending on who the end-users of a spreadsheet are, user-friendliness may be in order so a Combo box approach would be more appropriate then.

I think that the pivot table will do the same and maybe easier