Sunday, August 11, 2013

The SUMIF: A very simple yet powerful formula.

Learning this formula can open up a new world to novice Excel users. I've used the SUMIF function in everything from time-sheets to financial statement 
compilation.
It’s very easy to set up and it can have a profound effect on how you record and report information.

On the top of the spreadsheet we have a summary section where we’ll use SUMIFs to calculate the sales for each region.

Starting in row 15 we have a list of sales transactions with each record containing the Sale Number, Sale Amount, and Region Number. There are a total of 50 transactions listed.


Let’s create the SUMIF formula for our summary section.

In cell C3  I typed the following formula:
=SUMIF($D$16:$D$65,A3,$C$16:$C$65)

Refer to the graphic below for an explanation of the formula.

Next, we can copy the formula in C3 (Ctrl+C) and paste it (Ctrl+V) down the rest of the cells in our summary (C4 through C12). In C13 we can simply sum each region to get a total. In C13 I typed =SUM(C3:C12)


That's a basic SUMIF function.