Formula Power
How to
Create Summarized Financial Statements with SUMIFS Criteria
Lists
In one formula, you can summarize any number of specific
accounts in a list, adding or subtracting them as you want. This
makes it easy to summarize financial data from an accounting
trial balance.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

In
How to Use SUMIFS with Criteria Lists, Summarizing
Sales, I explained how to enclose a SUMIFS function in a
SUMPRODUCT function to cause your SUMIFS formulas to return the sum of items in a
criteria list.
In this article, I'll explain how to apply this functionality to financial
data, which often has specific challenges.
You can
download the workbook
with all examples here.
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, 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 and losses
as negative.
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
signadjusted 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 in 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, doubleclick 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, doubleclick 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. You can
download the workbook
with all examples here.
