Beyond the simple =SUM function in Excel

Office for Mere MortalsLast updated: 17 July 2018

We look at a few Excel functions that are beyond the basic level of the =SUM function. We see how they work and tell you when they can be applied.

By Helen Bradley

If you’re fairly new to Excel you’ve probably cranked out a few Sum functions and added and subtracted a few cells. Sooner or later, however, you’ll run slap bang into a problem that simple math won’t solve. Then it’s time to use one of Excel’s built in functions to do the job.

I’ve rounded up a few of my favourite “beyond =sum” functions for you this week.

I’ll show you how they work to sum values in different situations and how to use them.

Armed with this new knowledge I’ll also show you how to find instructions for using any Excel function and, most importantly, how to find the functions themselves.

DO TWO THINGS AT ONCE

If you’re creating, say, an inventory list, you may have cost prices of each item and the number in stock in two columns of the list. If you need a valuation of your inventory you could laboriously multiply each cost by the number of items and then total the results, or you could get Excel to multiply and add in one simple process.

The function that does this is =SUMPRODUCT and it takes as its two arguments the two ranges of cells that you want to multiply by each other. If the word ‘argument’ is confusing, think of it as the things you have to give Sumproduct for it to do its work. So the arguments are the ranges containing the prices and the stock numbers, without these, Sumproduct can’t give you the answer because it doesn’t know what the question is. You’ll see arguments mentioned a lot in Excel because most functions require one or more arguments.

Back to Sumproduct, if the cost of each item in your inventory appears in cells B5:B50 and the corresponding number of each item you have in stock appears in cells C5:C50 this formula calculates what your stock is worth:

=SUMPRODUCT(B5:B50,C5:C50)

The two arguments this function takes are the two ranges to multiply and it works by multiplying B5 * C5 and B6 * C6 and B7 * C7 etc.. and then adding the results.

TOTAL THEM IF…

Another of the handy functions which add numbers but do it more inventively than SUM is the SumIF function. Consider the situation where you have a list of sales people and the amount of sales they made for the month. You need to calculate the amount of sales which were made by sales people to whom you pay commission. One column in your table contains a Y or a N depending on whether the sales person is on commission or not. Sumif can count this for you and it takes three arguments, the range containing the figures to add (the sales figures), the condition to match (a Y), and the column the condition occurs in (the commission column).

Here’s an example, assuming the person’s name is in column A, the sales made in column B and the Y/N value in column C:

=SUMIF(C4:C10,”=Y”,B4:B10)

This example shows SumIF with three arguments, and the third of these is said to be optional. In our scenario we had to use all three arguments to get the answer to the question we posed, so from our point of view the argument wasn’t truly optional because we had to use it. The ‘optional’ part about the argument is that if you omit it SumIF will still work, is just does something a little different.

Consider this function:

=SumIF(B4:B10,”>500″)

This is SumIF without the optional argument. When you omit this third argument, SumIF checks the condition (value larger than 500) in the stated range (B4:B10) and sums the range. The result is like saying, add all the values in the range B4:B10 if they are larger than 500.

Sumif has a close relative called Countif which, instead of summing the values in the range specified, simply counts how many matches there are. In our sales person example, Countif will count how many sales people we pay commission to if you use this formula:

=countif(C4:C10,”=Y”)

The countif function always takes only two arguments and both are compulsory, it won’t work without them.

HOW DO I KNOW ALL THIS?

By now you might be asking how I know what SumIF needs and in what order? The answer is in Excel Help. Choose Help, Microsoft Excel Help and type Sumif to find the help entry for it and view it. The function Sumif is given a help page all of its own. At the top is a description of what the function does “Adds the cells specified by a given criteria” and then its syntax.

A function’s syntax tells you how to write it – the arguments in bold are compulsory, it won’t work without these. Those in regular type are optional in the sense that the function will work without them. However, you might have to include them when you write the function to make it give you the answer you are looking for.

You will also see a description of each of the arguments. Some are ranges, some are criteria etc. Further down the page, the Remarks includes any traps, warnings or other information that is handy to know about the function. It’s important to read these – for example, they will usually explain how the function works if you omit an optional argument – in some cases a particular behaviour is assumed so you will want to know if this is the case.

Still further down the page, the worked example shows you the function in use. You can copy and paste this into a worksheet to try it out. You will also find details of associated and similar functions and a link to these.

When you’re working with functions, the Excel help is an essential tool. It is well worth spending a few minutes reading and understanding how Help describes functions and it’s best to start with functions you’re already familiar with using so you’re not totally overwhelmed by the language.

HOW TO FIND FUNCTIONS

All the talk so far begs a far larger question: How can I use a function when I don’t know it even exists? It’s one thing to be able to write a SumIF function that works, it’s quite another to know that it exists for use in the first place. This is where your powers of research will come handy.

A good starting place to find a function to perform a task is to choose Insert, Function. The Insert Function dialog helps you find functions so, if we were wanting to know how to multiply and add numbers at the same time we could type “multiply and add” in the Search for a function area. This returns a list of possible functions – click each in the list and you can read a brief description of what it does.

When you find one to use, SUMPRODUCT in this case, click Ok and the Function Arguments dialog appears. Here you can read the arguments which are required and those that are optional. You can drag over the relevant areas of the worksheet to select the ranges to use in the function and the Formula result area of the dialog shows the result for the ranges you’ve selected. When you’re done, Excel enters the function into the cell for you automatically.