Content area
Full Text
Add data by up to 30 criteria.
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 apparendy 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 SUMlF 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 errorprone 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)l 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(A3A1 5, D3, B3:B1 5), 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 Al 5. 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...