The Sumif formula

Definition

This formula is a very handy formula to use. U will use this to extract figures from a big database. When you combine this with a Vlookup formula, you can really do magic with this and do reports you did in 2 weeks in 5 minutes. When you get data of hundreds or thousands of lines, you can add some figures with the same characteristics, for instance the same month, Group of accounts, same province or anything Yes, you can do this with a Pivottable, but when management expects a report in a specific format, a Pivottable will not work.

The makeup of the formula

The sumif formula consists of 3 components:

• The range field: This is the column in which the characteristics are.

Let’s show what we mean. We have the following data.

We have two possible range columns, the months and the groups. This is where excel must search for our criteria to total up. Now, we can do a sumif on the months which will effectively give us the gross profit per month, or we can do sales or a cost of sales total.

• Criteria field: In this field, you will put the criteria to be summed, for instance the specific month which must be summed, or the sales or cost of sales figures.
• Sum Range Field: Here the columns will be stated which must be totaled, which will, in the above table be the third column.

If we want the gross profit on certain months, we will do the following:

If we want the sales and Cost of Sales figures, we will do the following:

The nice thing about sumif is that if there is no data found, then the answer would be zero not an error.

Using the vlookup formula and the sumif formula together

So how can we use this with a Vlookup? If we have account codes or names that stay the same every month we can use a Vlookup to always allocate the codes correctly. We can then put in an allocation sheet, to allocate this group to certain other groups like follows:

Entertainment, legal, telephone and postage can be allocated to “Expenses” for instance. If we have our data, we can do a Vlookup to our allocation sheet to tell excel what the new group is.

Example

Our system can make the following download into excel

This may seem like little data but remember some companies may have thousand of lines per month like this one.
So we have a standard management report that look like follows:

We can redo the report every month, or use a combination of sumif and Vlookup formula and automate the process.
First we need to allocate our code to something by using Vlookup.
We need to create another sheet and only copy the code and description there (if you want to easily know what codes there are, use a Pivottable)
Then we must only write the description of the group in which they fit in next to them (it’s easier to write the same names of groups that are the same as your report, like “sales”, or “operating expenses”)

This sheet will look like follows then:

Now, we must do a Vlookup in our download to tell excel to which group each amount belongs. Like follows:

We can now easily do our report by sumif formulas
Remember the 3 fields of sumif

• First the range, will be our groups, because this is the range in which our characteristics must be found
• Secondly our criteria is the line item in our report that must be found, like “sales” or “operating expenses”
• The thirdly we must state which columns must be added up, this is our amount column

Our report would then automatically be generated like follows:

We will explain a detailed example later in the book. We will also explain how you can know check the figures easily with Pivottable and also easily sort out auditors queries.

Troubleshooting

The following common mistakes are always made:

• The thing people get wrong most of the times are when they attempt to copy the formula down or to the right, then the ranges copy with this. If for example column C:C changes to column D:D, then the wrong column will be totaled. Fix this with F4
• Remember to put the correct criteria in. If you want to total all the amounts for March and the word March has a space with it (“March “), then it does not match up exactly and will not be totaled.
• Make sure the range in criteria field is the same length and starts in the same row, as the range in your total field. This may cause some problems.

If you put a formula in the middle criteria field, make sure to close the brackets.

Things you can do with the sumif formula

• Do monthly reporting without changing the format...contact us for more info
• Use the sumif formula in combination with the concatenate formula to sum with more categories...contact us for more info

<-----Back to index

Next topic----->