Supercharge Your Excel Sum Operations: Add Data by Up to 30 Criteria
J. D. Kern · Journal of accountancy online/Journal of accountancy · 2009
[ILLUSTRATION OMITTED] Many CPAs, frustrated by rind 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 dear 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. As you can see, SUMIF requires three pieces of data. The first is the list of criteria to check for the desired value (that is, sales by Alice). In this example, the salesperson for each transaction is listed in cells A3 through A15. That range is the first element in our SUMIF formula. Second, SUMIF needs the selection criterion to apply when searching the range specified in the formula's first element. Because we want to know the sum of Alice's sales, we instruct SUMIF to search for Alice's name--the contents of cell D3. That cell's address is the second element in the SUMIF formula. Finally, we specify which values to sum when the criterion in the formula's second element is satisfied (that is, when the contents of any cell in the range A3 through A15 equal the contents of cell D3). For rows meeting that condition, SUMIF will total the related sales amounts in the range B3 through B15. That range is the final element in our SUMIF formula. Using this formula, Excel summed in cell E3 all of Alice's sales. To calculate sales for Jim, Samantha and Tom, insert similar formulas in cells E4, E5 and E6, respectively DOUBLE-BARRELED CRITERIA Now let's consider a harder case. Like the first one, it requires painstaking attention to detail. But this time, the process is more complicated. Instead of having to report only total sales for each salesperson, you have to calculate their sales for each month covered in the data you downloaded. SUMIF can't help you now; all it can handle is one criterion. You could make each salesperson's name that criterion. But you also have to sort and add by sale date, and SUMIF's three elements (salesperson for each transaction, individual salesperson, and sale amount) would be used up, leaving SUMIF incapable of evaluating sale date. What should you do instead? [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] Here's where an apparently ill-suited Excel function, SUMPRODUCT, can help. At first, it may not seem like an ideal fit. SUMPRODUCT's syntax [SUMPRODUCT(array1,array2, array3 ...)] is designed to multiply corresponding components in specified arrays, and then calculate the sum of those products. …