Supercharge Your Excel Sum Operations

Add data by up to 30 criteria.

July 2009
by J.D. Kern/Journal of Accountancy

Many CPAs, frustrated by rigid and inadequate reports from their general ledger or other enterprise systems, turn to Microsoft Excel. Nimble but powerful, Excel often manipulates data faster and more effectively than less agile applications. But to perform certain tasks optimally, a CPA sometimes may have to bypass what apparently is Excel’s most relevant function and instead use another Excel function that at first may not seem suitable. This article presents such an instance, comparing the SUMIF and SUMPRODUCT functions and demonstrating an innovative approach that can produce the reports you need, quickly and easily.

Let’s begin by automating a simple but tedious and potentially error-prone data analysis and reporting process. Here, a well-known Excel function does the job perfectly. Later, we’ll look at a harder task that requires a more complex — but very workable Excel solution.

Say you want to calculate the total sales for each member of a team, but your GL or other enterprise system can’t do the job. So you export the relevant data into Excel, where you use the SUMIF function [SUMIF (range, criterion, sum_range)] to cull and add up the sales transactions for each salesperson. It’s clear this function can save a lot of work by automating the addition of sales selected according to a single criterion, such as a salesperson’s name.

Exhibit 1 contains sales transactions for four salespeople, one of whom is Alice. To calculate her total sales, we use the formula in cell E3: SUMIF(A3:A15, D3, B3:B15), which correctly reports that Alice’s three sales ($100 + 300 + 350) add up to $750.

This article has been excerpted from the Journal of Accountancy. View the full article here.