"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Sum Your Data Based On A Condition

SUMIF is one Excel's many extremely useful worksheet functions. The uses for this function are almost unlimited, so to help you understand how it works and how you can apply it, I will describe a scenario for you.

Let's assume you have a table of daily sales data with columns containing Dates (col C), Names (col D), and Sales (col E). You want to determine the total sales for a particular salesperson without disturbing the original data.

In a cell outside the table, here's what your formula might look like:
=SUMIF(D2:D32,"John Smith",E2:E32)

A function is comprised of arguments that are used to perform the calculation. SUMIF has three arguments.

In the example above, D2:D32 is the Evaluation Range, the first argument of our SUMIF function. It tells Excel which range to evaluate. The range for this argument must be only one column wide.

The second argument we need is the Criteria. This can be a constant value, a cell reference or a formula that produces a valid value.

The third argument of the SUMIF function is the Sum Range. This is the range we want to get the values from whenever our criteria matches the evaluation range. When an item in the evaluation range (D2:D32) matches the criteria "John Smith", the value of the corresponding item in the sum range (E2:E32) is added into the total.

In our little scenario, this would give us the total of all sales in our table for John Smith.

Note also that you can use the Function Wizard to guide you through the construction of your SUMIF function. Just click the equal sign in the formula bar and then click on the drop-down arrow to the left. If SUMIF is not listed, click More Functions and you'll find it in the Math & Trig category. The wizard will guide you from there.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.