SUMIF is a largely misunderstood Excel Function, which under the correct circumstances can be very useful. The basic formula in this case looks like this:

=SUMIF(Range,Criteria,Sum_Range)

Essentially this function will allow you to selectively apply the SUM function to those rows of a data table which match certain criteria. For example if you had a data table containing stock information, you might want to find the number of stock units you currently held which have a certain sell-by date. The following is a very simplistic view of what that data table might look like:

Following our example, let’s imagine that we want to know how many stock items we have that have a sell-by date of 10/05/2013. In this case we can see quite easily that the Bread, Milk and Donuts have that sell-by date, and that in total we have 20 units of those products – Of course in reality you’ll probably have much larger data tables than this.

Going back to our basic formula, this is how we’re going to fill it in –

**Range** –
This is the range that you want to match against certain critera, so in our
case it’s going to be column D; Sell-by Date. We can select the cell range
specifically or the whole column, depending on your preference. For now I’ve
chosen to go for the exact cell range: D2:D9

**Criteria** – This
is the criteria that you want to match the Range against – It’s usually easiest
to simply type the criteria you want to use into an unused cell and then use
that cell reference in your formula.

**Sum
Range** – This is the range that you’re going to SUM based on
the **Range** and **Criteria**. If you leave this blank Excel will use the **Range** you’ve already specified as the **Sum Range** as well. In this case we want
to use column C, and again I’ve elected to use the precise cell range
reference: C2:C9

Our completed formula is as follows: =SUMIF(D2:D9,F2,C2:C9)

Of course this formula is useful for more than just stock
audits, and it isn’t just confined to comparing dates. Your **Range** and **Criteria** cells could contain numbers, letters, codes, etc. so long
as your **Sum Range** contains only
numbers.

If you’re still struggling with the SUMIF Formula you might find it helpful to have another look at the Getting started with Excel formulas page.

Looking for more? Return to Excel Formulas or the Office Software homepage