In How to Use SUMIFS with Criteria Lists, Summarizing Sales, I explained how to use the SUMPRODUCT function with SUMIFS to return the sum of items specified by a criteria list.
In this article, I’ll explain how to apply this functionality to financial data, which often has specific challenges.
Example 4: Income Data
Financial statement data typically is exported in one of two ways.
First, it can be exported with Debits as positive numbers and Credits as negative numbers. Under this method, revenue is a Credit and expenses are Debits. This allows you to sum any numbers without worrying about their sign.
Second, financial data can be exported as positive values if they have their natural sign, and negative otherwise. For example, profits (a Credit) are positive, and negative profits—losses—are negative.
This figure illustrates the second scenario.
Here, both revenues and expenses are positive numbers, and it’s up to us to decide which numbers should be added or subtracted for reporting.
Now suppose we want to calculate Net Profit from this very simplified example of P&L data.
Like the examples in Part 1, How to Use SUMIFS with Criteria Lists, Summarizing Sales I have a criteria list, which I call AcctList. I also have Start and Stop dates, as in Figure 2 in Part 1 of this series.
But I also have a sign associated with each account. In this figure. The sales are positive and the expenses are negative because I want to report profits as positive (debits) and losses as negative (credits).
The Profit formula in cell H11 uses these resources to calculate the number I need. Here it is:
H11: =SUMPRODUCT(SUMIFS(Amt, Acct,AcctList, Date,”>=”&Start, Date,”<=”&Stop) *Sign)
Here’s what this formula tells Excel to do…
Sum the Amt column for each Acct in the AcctList list, but only for the dates between the Start and Stop date settings. Next, multiply each total found from the list by its Sign value. And finally, use the SUMPRODUCT function to add up all those sign-adjusted sums to give us the amount of our Profits.
Testing the Formula
That formula does a lot of things, so we really need to test it. We could use several formulas in the Test column, but this probably is the easiest:
E3: =(B3>=Start)*(B3<=Stop)*SUMIFS(Sign, AcctList,C3)
This formula performs two tests and a lookup. It returns zero if either of the tests fails or if the lookup fails; otherwise it returns the sign for the account from the Sign column.
First, we test whether the date in cell B3 is greater than or equal to the date specified by the Start cell. If the test is true, this part of the formula returns TRUE, or FALSE otherwise.
Second, we test that the date also is less than or equal to the Stop date. Again, the formula returns TRUE or FALSE.
Third, we use SUMIFS like a lookup formula. We sum—that is, we look up—the Sign when the AcctList range has a value equal to cell C3. If the Acct value in cell C3 doesn’t appear in the AcctList, the SUMIFS function returns zero.
And finally, we multiply the tests and the lookup. If both tests are TRUE, and the lookup finds a sign, Excel returns the sign; otherwise it returns zero.
And then we copy cell E3 down the column, as shown.
We do all of that merely to return the Sign value for each account in the AcctList—when it’s within the specified date range—or zero otherwise.
And finally, the Test summary formula is easy:
H12: =SUMPRODUCT(Amt*Test)
This formula merely multiplies the column of amounts by the column of tests, and then sums the results.
And luckily, the values in cells H11 and H12 are equal.
The Gross Profit Formula
Often, in any profession—not just in Finance—we define values and ratios that we calculate from a table. The Gross Profit calculation is just such an example. The Gross Profit is merely Sales minus COGS (Cost of Goods Sold).
Here’s the formula for the Gross Profit value:
H14: =SUMPRODUCT(SUMIFS(Amt, Acct,{“Sales”;”COGS”}, Date,”>=”&Start, Date,”<=”&Stop) * {1;-1})
In this formula, rather than referencing a list, the Acct criteria argument substitutes an array constant, {“Sales”;”COGS”}. And a second array constant, {1;-1}, adjusts the signs before SUMPRODUCT returns the total.
If you’re familiar with array constants, you could just type them as you’re setting up the formula. But let’s suppose you’d rather not do that.
Therefore, to set up this formula, let’s start by copying the Profit formula to cell H14:
H14: =SUMPRODUCT(SUMIFS(Amt, Acct,AcctList, Date,”>=”&Start, Date,”<=”&Stop)*Sign)
Now, in your formula bar, double-click on the AcctList name, which will select it. Then, with the name selected, press F9, which will return the values for that range, as an array constant. Next, double-click on the Sign argument, and press F9 to return its values, again, as an array constant.
At this point, the formula in your formula bar should look something like this:
H14: =SUMPRODUCT(SUMIFS(Amt, Acct,{“Sales”;”COGS”;”Admin”;”Taxes”}, Date,”>=”&Start, Date,”<=”&Stop)*{1;-1;-1;-1})
Because the AcctList list included items that aren’t part of the Gross Profit calculation, you just remove them from the formula in the formula bar. That is you remove ;”Admin”;”Taxes” and ;-1;-1 and then you press Enter, giving you:
H14: =SUMPRODUCT(SUMIFS(Amt, Acct,{“Sales”;”COGS”}, Date,”>=”&Start, Date,”<=”&Stop)*{1;-1})
In Part 3, I complete this series on criteria lists.
