SumIf's little known function

SumIf is a great function that a lot of Excel users don’t know up about. Basically it combines two very popular functions - the Sum function and the IF Function. SumIf tests specified cells and if those cells meet certain conditions or “criteria” then selected cells are summed up.

Think of the possibilities: You may wish to sum up receipts by different lockboxes or sum up the value of past-due invoices. Or you may just want to add up negative numbers in a column quickly or add up the returns or overtime for a particular day.

I am using a very simple example to illustrate SumIf. In the example below, I tested to see if any of the house prices in cells A3: A6 exceeded 150,000 so that I could sum the related realtor commissions. In looking at the example, you can see that there are two houses that meet this criteria (at row 4 and 5) and that the corresponding realtor commissions are in Column B.

The house prices at row 4 and 5 met the specified criteria so the corresponding values in Column B, (12,500 and 10,000) were added up to calculate total commissions on houses with prices over $150,000.

So, basically, the IF component checked to see if any houses were over $150,000 and then the SUM component added up the corresponding realtor commissions associated with those houses in Column A.

Notice that I started my range at A3 instead of A2. It is important to remember not to include your headings when you select your data as this may result in an incorrect answer. If you have a terrible memory and don’t like to fuss around then select the entire column by clicking on the column letter so that you don’t have to worry about it.

Second, if you use IFs frequently, you may have already noticed the quotation marks around the criteria. SumIF is not considered a logic function. It is considered a math and trig function so the syntax is a bit different and quotation marks are needed if you type the criteria in as I did. Excel is forgiving and will automatically insert them if you forget. If the criterion used is a cell reference, such as b12, then you would not need to use the quotation marks; however, the contents of the cell would need to include the operator if there was one. In my example, b12 contains >150000.

Have fun. I think you will definitely find this useful. By the way, if you like SumIF, then try CountIF. It works the same way only of course it counts instead of summing.

Patricia McCarthy is the co-owner of CPASelfStudy.com, an on-line CPE provider. She is also a software trainer and a frequent discussion leader at the Indiana CPA Society. You can contact her at patricia@cpaselfstudy.com.

You may like these other stories...

In the old days, we used to tape down receipts from our travels and submit them to accounts payable. But that was before remote employees who may live in a different city from the home office. And of course, there's all...
In 2011, electrical services and technology provider Parsons Electric in Minneapolis, Minn., decided to take its accounting to the cloud. Monica Ross, the company's director of strategic projects, talked with AWEB about...
Event Date: July 24, 2014, 2 pm ET In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the...

Upcoming CPE Webinars

Jul 16
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
Jul 17
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.