Wednesday, January 19, 2005

SUMIF Formula in Excel

The SUMIF formula is a very powerful tool for analysing data.
It allows users to identify subtotals in a range using a criteria that you set.
For example a range of salary data containing names and weekly pays in two columns A & B.
Using SUMIF you can simply and easily calculate the total pays made to any employee.
The SUMIF formula uses the following syntax.
=SUMIF(criteria range, criteria, sum range). The sum range and criteria range can be the same if necessary.

Usually this would mean that you would write the formula for our payroll example above as follows:
=SUMIF(A:A,"name",B:B)
You can replace the "name" with a cell reference to an input cell (or a named range) thus allowing users to change the name or even use data validation to create a drop down list of potential names.
As an auditing tool in this instance it is useful as well.
Creatg a table of the unique employee names and add the formula beside each. The total of these individuals' pay totals should equal the total of all pays in column B. If not then there is either an employee name that hasn't been added to your summary table, or there are typing errors in column A, Eg spaces before or after a name etc.


Posted by: Jethro | 7:53 PM |