11-Apr-2013

With Office 2007, Microsoft introduced many changes to Excel including the big grid and the ribbon interface. One of the most useful "small" updates was the introduction of two new formulas, SUMIFS and to a lesser extent COUNTIFS, which are like the big brother versions of SUMIF and COUNTIF.

The difference between SUM and COUNT can be explained by considering a shopping basket with several items. If you want to know how many items, you COUNT them, if you want to know how much it will all cost, you SUM all the item's prices. Although the article will focus on the "SUM..." side, the "COUNT..." side is very similar.

One word of caution, the order of the arguments differs for the SUMIF and SUMIFS formulas. If you have never used the SUMIF formula, I suggest you start with the SUMIFS formula, as it can do everything the SUMIF formula can.

As an example - perhaps bizarrely - John is analysing the cost of his shopping basket contents based on food group. He has created a small table with food groups and their respective prices. In C13 he has entered the SUM formula to get the total price.

To get the total spent of Fruit, we can enter the SUMIFS formula into C15. Note that criteria is case insensitive.

Let's look at the formula in detail. The first argument is the sum range C3:C12, this range contains the items we want to add up. The second argument is the criteria range B3:B12, and the third argument is the criteria "fruit".

In this case, Excel will only add the prices in the cost column, where corresponding - same row - item meets the criteria "fruit". So for example 2.55 in C6 is included in the sum because B6 contains "Fruit".

Note it is possible to have wildcards in the criteria, a question mark (?) matches any single character, an asterisk (*) matches a sequence of characters. To specifically match a question mark or asterisk, precede it with a tilde (~).

Now we can slightly modify this formula, by putting the criteria into a cell rather than embedding it in the formula.

This is more flexible, because if we want to see the total spend on Veg, we can just type that into cell B15.

The great thing with the SUMIFS formula is you can add more than 1 "criteria range - criteria" pair, in fact up to 127 condition pairs although if you go have more than 4 or 5 conditions a different approach might be better.

To illustrate multiple criteria let's consider that we want to add up all the fruit that costs less than 3.00.

To do this we can extend the formula to include a second criteria range C3:C12 and the criteria "<3". Note that the criteria is in quotes.

Again we can take the criteria value out of the formula and put it into a cell, in this case B16. In this case the criteria part is "<"&B16 . Notice that the test - the less than - is still in quotes, we then have an ampersand followed by the cell reference.

Below is list of the different tests that can be applied.

How SUMIF differ from SUMIFS. Firstly as seen above SUMIFS can have multiple conditions, that is 1 or more criteria range - criteria pairs. The second difference is the order of the arguments. Compare the two equivalent cases below.

There is one more subtle difference. In all of the examples above we have summed over a range which is n rows by 1 column. Equally we could have summed over a range which is 1 row by n columns. However both SUMIF and SUMIFS can sum over a range that is n Rows by m Columns. However if the criteria range is not the same size, SUMIF will give a result but possibly not what you are expecting, whereas SUMIFS will throw a Value error. In the example below we want to sum the values for "cat" across columns D and E.

If the criteria range and sum range are the same size then the formula will work as expected.

If you are using n by m ranges where n and m are both greater than 1 in SUMIF or SUMIFS formulas, take care!

Screen shots created in Excel 2007 on Windows 7