Question: I have a sheet of sales information and would like to add together all lines for a specific sales person. How can I do this?
Answer: By using the SUMIF function
Process (Excel 2003, 2007 and 2010):
The SUMIF function is very useful, allowing you to total a number of lines together based on one common trait. This could be a product name, a region or category, or a particular person’s name. The syntax of the SUMIF function is as follows:
=SUMIF(range,criteria,[sum_range])
In the syntax, the range is the column that you’d like to search through for a given value (the criteria), and when it finds it, add the appropriate value from the [sum_range] column. This is based on the row that the criteria is in – if the term ‘chocolate biscuits’ is in column A, rows 6, 9 and 12, with the product sale values listed in column C, then the SUMIF formula:
=SUMIF(A:A,”chocolate biscuits”,C:C)
would add cells C6, C9 and C12 together.
In this example, we’re going to use the following table of information. Column H has a unique list of all sales people in column A, and we will use the SUMIF function to add the total product sales together for each sales person.
1. Select cell I2, type in the formula =SUMIF(A:A,H2,F:F) and press Enter.
This will search through column A for all references of the content of cell H2 (in this instance “Anderson P”), and then add the appropriate value in the cell in column F for that row.
2. Double click on the Auto-Fill handle in the bottom left hand corner of cell I2.
This will automatically fill the formula down to cell H14. By referencing cell H2, the formula will increment automatically, meaning that we don’t have to type in each and every value – they can be typed into each of the H cells, or changed in those cells much more easily than editing the formula.
