Once you've worked with Excel for a bit, you'll no doubt have come across different functions all containing SUM such as SUM, SUMIF, SUMIFs and DSUM. This article describes how these different functions are used to help you carry out various data analysis tasks which all involve summing. These functions differ in how you can use criteria with the SUM.

The SUM Function

The SUM function as you know sums all the cell values within the specified range. Empty cells and cells containing text have a zero value. The Autosum command automatically creates a SUM formula in the currently select cell, with all numbers above the selected cell included within the SUM.

The SUMIF Function

The SUMIF function allows you to apply a SUM to a data range, with a condition criteria. The criteria can apply to the data range itself or to different cells in a criteria range. The function consists of these elements with each separated by a comma =SUMIF(CRITERIA RANGE, CRITERIA, DATA RANGE).

For example suppose you're working with a product data table with the end column containing product prices. You can use SUMIF to sum the prices but only for products which match a certain criteria. The criteria might be a value in one of the other table columns. So when you use the type =SUMIF followed by an open brackets you first specify the criteria range by dragging the appropriate cells, type a comma, then click once on a cell containing the required criteria, type another comma, and then specify the data range. If you don't type a separate data range, Excel assumes the data is the same as the criteria range. By the way, if you do specify a criteria range, then a criteria, Excel assumes the data range is exactly the same size as the criteria range. You then only need to select the first cell of the data range. So SUMIF lets you sum a range of target cells using a condition criteria based on the data cells, or on other cells in the table.

The SUMIFS Function

For users of Excel 2007/2010 the SUMIFS function takes this one step further. Unfortunately SUMIFs is not available in Excel 2003.

The SUMIFS function allows you to apply a SUM to a data range, with multiple criteria (up to 127). You'll find that the elements within SUMIFS have been changed around compared to SUMIF. The SUMIFS function consists of these elements with each separated by a comma =SUMIFS(DATA RANGE, CRITERIA RANGE1, CRITERIA1, CRITERIA RANGE2, CRITERIA2, and so on). So provided you take note to specify the data range first, then for each criteria the range, then the criteria, the function is straightforward to use, although it can result in relatively long formula.

SUMIFS gives you the capability to apply a SUM to a range of cells with multiple condition criteria. And in a similar way to criteria in SUMIF, the actual criteria ranges do not have to be in the same table as the target data. SUMIF and SUMIFS finds a relative position in the target data cells derived from the relative position or positions of one or more criteria within their respective criteria ranges.

The DSUM function

The DSUM function allows you to sum data within a table using multiple criteria based on the same table. However you can specify the criteria in a separate table provided the same field names are used compared to the originals. So you can copy the original table headings and paste to a new location to create a criteria table. In this criteria table you can enter criteria data under the various table headings. Criteria can include arithmetic operators and values, such as" >50" (without the quotes) in a cell under a numeric field, or text criteria such as "Red" (without the quotes) in a cell under a Text field containing colours.

The DSUM function consists of these elements with each separated by a comma =DSUM(DATABASE RANGE, FIELD, CRITERIA RANGE) where DATABASE RANGE is the original data table including headings, FIELD is the target column you want to sum and CRITERIA RANGE is the criteria table. So DSUM will sum the target field subject to the criteria table data. So if you have the text RED under a field called Colour, say, then the sum will only include table records which have the colour red.

The power of DSUM lies in the fact that you can include multiple criteria all within the same criteria table. If you include two (or multiple) criteria across the same row in the criteria table, then DSUM will only sum the target data where the original table records match each criteria. If you include two (or multiple) criteria down a column under a criteria field, then DSUM will sum the target data where the original table records match the first or second criteria down the column, so both (or all) sets of records will be summed.

Hopefully this article has given you a brief insight into the power of the different Excel functions SUM, SUMIF, SUMIFS and DSUM. There are lots more really useful functions in Excel and a really effective way to learn more about these is to attend a training course. The best ones are hands on with lots of class interaction and guidance.