Almost every Exel user has encountered the SUM function. It is one of the most widely used functions of the Excel functions. And most Excel users will also have used the IF function. SUMIF function is a combination of the SUM and If functions which allows us to calculate the total of all cells within a given range that match a certain condition.
For example, let’s say that, we have a “Sales” worksheet containing a breakdown of the sales of each salesperson. We now want to create a summary of these figures in a “Branches” worksheet containing two columns: “Branch” and “Total Sales”. We can use the SUMIF function to generate the figures in the “Total Sales” column.
A good first step would be to create named cells so that we can refer to these names in our formula. To name a range of cells begin by selecting the range then click on the name box in the top left of the worksheet, enter a name then press the Enter key.
Having inserted the names of all the branches in the first column of our “Branches” worksheet, we would highlight the first cell in the “Total Sales” column, adjacent to the cell containing the name of our first branch; let’s say, for example, our first branch is “Birmingham”. When using functions for the first time, it’s useful to use Excel’s Insert Function facility. To access this, click on the Insert Function button on left of the formula bar. The SUMIF function is in the “Math and Trig” category. Scroll down the list, highlight SUMIF and then click OK. Excel now prompts us for the three arguments required by the SUMIF function.
The first argument is the range of cells that we want to evaluate. In our branch sales example it would be the column containing the names of the branches. If we have named this column, we can insert this name by clicking on “Use In Formula” in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing all the names in the workbook.
The second argument is the criteria we want to match. In our example, is simply the contents of the cell in the adjacent “Branch” column, which in this case contains “Birmingham”. We can click in the cell to pick up the reference.
The final argument is the SUM range; the column that contains the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we would click on “Use In Formula” and choose the name. Once we have specified the three arguments, we click OK and Excel creates the formula.
It is now safe to copy the formula down. The cell reference of the adjacent column containing the branch name will alter but the two named ranges will remain the same. To copy the formula down, simply position the cursor over the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.
Click here if your staff need in-company Microsoft Excel training anywhere in the UK.
February 1, 2010 by Gill Harrison