Most Excel users have encountered the SUM function. It is one of the most widely used functions within the program. And most Excel users will also have used the IF function at some stage. The SUMIF function is really just a combination of SUM and If 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 individual salesperson. We then want to create a summary of these figures in a "Branches" worksheet where we want to create a total sales figure for each branch; so our "Branches" worksheet would have two columns: "Branch" and "Total Sales". We can use the SUMIF function to work out the figures in the "Total Sales" column.
A good place to begin would be to create named cells so that we can refer to these names in our formula. To name a range of cells we begin by selecting the range then we click on the name box in the top left of the worksheet, enter the name then press the Enter key.
Once we have 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 our first branch is "Birmingham". When using functions for the first time, it's always useful to use Excel's Insert Function facility. To access this, click the Insert Function button on left of the formula bar. The SUMIF function is to be found in the "Math and Trig" category. Scroll down the list, highlight SUMIF and then click OK. Excel will then prompt us for the three arguments required by the SUMIF function.
The first argument is the range of cells to be evaluated. In our branch sales example, this would be the column containing the name of the branch. If we have named this column, we can insert the name by clicking on "Use In Formula" in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing every name 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 and, in this case, it will be the column containing the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we can click on "Use In Formula" and choose the name that we created earlier. Having specified these three arguments we click OK and Excel creates the formula.
It's now safe for us to copy the formula down. As we copy the formula, the cell reference of the adjacent column containing the branch name will change but the two named ranges will remain the same. To copy the formula down, position the cursor on the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.
For example, let's say that we have a "Sales" worksheet containing a breakdown of the sales of each individual salesperson. We then want to create a summary of these figures in a "Branches" worksheet where we want to create a total sales figure for each branch; so our "Branches" worksheet would have two columns: "Branch" and "Total Sales". We can use the SUMIF function to work out the figures in the "Total Sales" column.
A good place to begin would be to create named cells so that we can refer to these names in our formula. To name a range of cells we begin by selecting the range then we click on the name box in the top left of the worksheet, enter the name then press the Enter key.
Once we have 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 our first branch is "Birmingham". When using functions for the first time, it's always useful to use Excel's Insert Function facility. To access this, click the Insert Function button on left of the formula bar. The SUMIF function is to be found in the "Math and Trig" category. Scroll down the list, highlight SUMIF and then click OK. Excel will then prompt us for the three arguments required by the SUMIF function.
The first argument is the range of cells to be evaluated. In our branch sales example, this would be the column containing the name of the branch. If we have named this column, we can insert the name by clicking on "Use In Formula" in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing every name 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 and, in this case, it will be the column containing the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we can click on "Use In Formula" and choose the name that we created earlier. Having specified these three arguments we click OK and Excel creates the formula.
It's now safe for us to copy the formula down. As we copy the formula, the cell reference of the adjacent column containing the branch name will change but the two named ranges will remain the same. To copy the formula down, position the cursor on the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.
About the Author:
The The writer of this article is a trainer and developer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 Classes at their central London training centre.
0 comments:
Post a Comment