99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Want To Get To Grips With The Different SUM Functions In Excel?
Sun 24th July 2011
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
EA To CEO
I found today's course a lot more insightful than I thought I would with practical tips that I can implement into my daily routine as well as some more long term goals.
One thing that would be beneficial would be access/visibility of the schedule/timings for the day, throughout the whole day. Although this is shared at the beginning of the day, it might be nice to be able to refer back to it at times e.g. if you are starting to feel tired/need a comfort break then you know roughly what is coming up next and can judge whether you duck out of the training for a few minutes or how long you have to wait.
I found it very helpful. Thankyou
L'occitane en Provence
Head Of Division
PowerPoint Intermediate Advanced
Great course, just the right amount of information. I wouldn't change it