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.