Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - Using SUMIF

The data-set laid out below in Microsoft Excel represents a straightforward invoice list example. We wish to list the total invoice amounts corresponding to each individual vendor using SUMIF.

To make things easier, we can set up Range Names. We do this by selecting the range of data we wish to work with and then clicking in the top left box which usually shows the cell coordinates. As can be seen in the example below, we have set up three ranges corresponding to _vendor _date and _amount We can use these Range Names in formulas instead of the actual coordinates.

Our next step is to create two new columns corresponding to Vendor and Amount. We list the vendors and next to the first vendor in the Amount column, we write the formula, as shown in the example:

These invocations of the set ranges are far easier to use than writing out a formula using absolute references such as =SUMIF($A$2:$A$18,E6,$C$2:$C$18) Of course, Range Names are also reusable on the same sheet.

Our final step is to copy down the formula and AutoSum to check that the total is still as it should be:

For more help with SUMIF or any other Excel formula, contact our experts. The Microsoft Excel Help page for SUMIF is here.