SUMIF

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

This Form cannot be submitted until the missing fields (labelled below in red) have been filled in
Let me know what you think:
Please note that all fields followed by an asterisk must be filled in.
First Name*
E-Mail Address*
Feedback*
Please enter the word that you see below.