Wednesday, June 17, 2009

Excel: SUMIF Function

A reader emailed me Tuesday to tell me that she had used my "How to Make a Check Register in Excel" tutorial for a project in a class she's taking. She thanked me for making the tutorial available ("You're welcome!") ... then asked if I could help her with one of the project's requirements — one that had her stumped.

The requirement that puzzled her? She had to use Excel's SUMIF function somewhere in her check-register spreadsheet.

After a few minutes of thought, I suggested that she add a column for Transaction Type (CKCD, CASH, CHK, DEP) if she hadn't done so already. She could then use the SUMIF formula in a cell somewhere to total up, say, all the deposits (type DEP) in the register. "Could be useful to someone," I wrote.

That was also approximately when I thought to myself: Hey! Why not do a how-to blog post for the SUMIF function?

So, those of you who are bored to tears by my Excel how-to posts can stop here and go do something exciting, like grabbing a rubber-band gun and thwapping the politicians on CSPAN. They deserve it; trust me.

Using Excel's SUMIF Function

Most folks know that in Excel, the SUM function is used to add up (sum) a group of numbers. The SUMIF function goes beyond that: It sums a group of numbers, yes, but it sums only those numbers which fit some user-designated criteria.

For instance, consider the following chart:

Sales Chart

We could execute the SUM formula on Column D, the Sales column, like so:


This would give us the total sales amount for all transactions. However, what if we simply want to know how much employee 180 sold? That's where the SUMIF function comes in!

The SUMIF function has three arguments...

SUMIF Function Arguments

... where:

  • "Criteria_range" is the range of cells which contain the criteria we're searching through;

  • "Criteria" is the specific criteria/condition we wish to match; and

  • "Sum_range" is the range of cells in which selected numbers will be summed.

So, in our case above, we wish to sum up the sales of employee 180. We'll put our SUMIF function in Cell C19. A finished version might look like so:

SUMIF Function in Action!

Our "criteria_range" is C5:C12 (the range of employee numbers). Our "criteria" is Cell C17, where we've input the employee number whose total sales we wish to calculate. And our "sum_range" is D5:D12, the range of sales figures.

With this info plugged into our SUMIF function in Cell C19, Excel tells us that employee 180 booked $50 of revenue. (And some quick gradeschool math tells us Excel is correct!)

For those of you who'd like to view it, my SUMIF Example Spreadsheet (.xls) can be downloaded as a ZIP file and extracted.

Labels: ,

— Posted by Michael @ 8:17 AM


Or, you could get fancy and use an "array formula" in cell C19 {=SUM((C5:C12=C17)*D5:D12)} to get the same result. The magic of Array formulas becomes apparent when you need to search multiple columns or criteria; such as "all sales for employee #180 (or the value in C15) AND with a transaction number of say, greater than or equal to 1017." {=SUM((C5:C12=C15)*(B5:B12>=1017)*D5:D12)}

ENTERING AN ARRAY FORMULA: To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel will display them automatically. If you neglect to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

The following is an excellent tutorial on array formulas:

Anonymous ExcelGeek
, at 9:24 AM, July 01, 2009  

I say again: Your knowledge of Excel has got to be unhealthy somehow.

Showeroffer! :)

** Comments Closed on this Post **

Thoughts on my personal finances, goals, experiences, motivations, and accomplishments (or lack thereof).

My financial life began turning around when I took responsibility for it.
— Dave Ramsey


Start (2005-12): ~$21,900
Currently: $0
[About Our Debt Paydown]


Savings Goal: $15,000
Currently: ~$15,115
[About Our Liquid Savings Goal]