24 Oct2011
Using the Excel SUMIF function to find that out-of-balance journal entry
One of the most underrated Excel functions that I use often is the SUMIF function. On my Excel function tournament bracket, SUMIF ranks slightly behind the SUM, AVERAGE, IF, COUNT, PMT, and RATE functions. Still, it has a shot at upsetting one of the higher seeds on any given day. The SUMIF function adds the values in cells specified by a given condition or criteria. Tim Gavin, a fellow CPA blogger, wrote a great blog post last week for the Sikich Technology Blog explaining how the SUMIF function works. I won’t reinvent the wheel here, so check out his post for a tutorial. I simply want to give an example of how I use it. Let’s say I’m cruising along in Excel making journal entries. Each entry is designated by a letter. Some entries have multiple debit and credit adjustments. Work comes to a screeching halt when I check the column totals and realize that my journal entries are out of balance by $200. It may be easy to spot the difference with only four entries in the screenshot I’ve provided below, but just imagine that I’ve exhausted the alphabet and am up to entry z. With so many adjustments, finding the discrepancy could become a painful and time-consuming ordeal.
Here comes the SUMIF function to the rescue. First, I enter the letters of the alphabet for each journal entry down a column (using the fill handle to drag down the letters sequentially saves some wear and tear on the keyboard). Then I enter the formula shown below and copy it down for each letter. The formula adds the amounts for the debit and credit column that correspond to each letter of the alphabet.
The results are in. Adjustments a, b, and c balance out. It was adjustment d that threw me off my game.
Trackback from your site.
Joel Ungar
| #
That was one I didn’t know about.
Please update if the rankings change!
Reply
Tim Gavin
| #
Brilliant. I’ve done similar things, but not with journal entries.
Reply
Michelle Edwards
| #
Impressive Dustin! My mind is spinning with possibilities on how I can use this. Thanks for the tip and for the link to the detailed instructions!
Reply
Jon C
| #
I love the SUMIF function! It saves so much time. In school sometimes we have assignments where we make a schedule of expenses then an income statement. Just put a SUMIF as the value in the Income Statement and you’re set.
Reply