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 
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:

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.