Last fall, I helped TechRepublic reader Charlie hash out an Excel SUMIFS() function,
but his needs expanded a bit in December. He had several criteria values that were across multiple sheets. If that’s not enough, we’ll throw in a monkey
wrench to further confuse things.

 

SUMIFS() can handle it all! The key is to
learn the Excel function’s syntax and then write it all out in
English. This step isn’t necessary, but if you’re just not getting
it right, it can’t hurt. (SUMIFS() is new in Excel 2007, so this article
doesn’t apply to Excel 2003 and earlier.) The downloadable demo file includes
the Excel workbook used in this article.

First, let’s review SUMIFS()’s syntax, where sumrange references the range of values you want to sum, criteriarangex references the conditional data values you’re checking, and criteriax is the specific conditional value for which you’re checking.

 

data-code=”SUMIFS(sumrange,criteriarange1,criteria1[,criteriarange2,criteria2[,…]])

That’s not complicated, but multiple
ranges and values on multiple sheets can get confusing and grow a bit unwieldy.
When that happens, break it down into the individual components and references
first.

 

The sheet in Figure A is a reasonable representation of Charlie’s
workbook, but imagine dozens of columns (all possible criteria) and thousands of
records. The example Data sheet contains the sum and criteria data ranges. The Summary sheet contains the specific criteria values. We’ll use SUMIFS() to summarize the values
on the Data sheet, creating a simple matrix.

Figure A

 

Now, let’s clarify Charlie’s requirements by breaking them
down into data ranges.

1. We want to sum the Amount values (Data!C5:C10).

2. We want to sum those values by the Year values (Data!A5:A10).

3. We want to narrow that sum by the Item values
(Data!B5:B10).

It’s easy to fill in the function’s arguments when referring
to this breakdown:

  • sumrange
    = Data!C5:C10
  • criteriarange1
    = Data!A5:A10
  • criteria1
    = Summary! B4:E4
  • criteriarange2
    = Data!B5:B10
  • criteria2
    = Summary!A5:A9

When you break it all down, supplying the arguments is easy:

 

=SUMIFS(Data!$C$5:$C$10,Data!$A$5:$A$10,Summary!B$4,Data!$B$5:$B$10,Summary!$A5)

Referencing is important. The sumrange and both criteriarange
arguments are absolute. The first set of criteria values are in row 4, so you
need an absolute row reference. The second criteria
values are in column A, so you need an absolute column reference. Both mixed
references will accommodate the matrix configuration. By entering the SUMIFS()
function (above) into Summary!B5 and copying it to the remaining range (B5:E9),
you create a summarizing matrix. Walking through all these details seems like a
lot of work, but it beats a function that returns an error value or worse — the
wrong results.

Figure B

 

The only instance where both criteria ranges and values
change the result is for the year 2013, item D15. In a real-world
sheet, this won’t be the case. I kept it simple, so you could easily see
how that function’s working correctly.

You’ve put to use several of Excel’s fundamental yet powerful
features:

  • The SUMIFS() function lets you specify multiple
    criteria ranges and values.
  • The function’s ranges and values don’t have to
    be on the same sheet.
  • Absolute and relative addressing lets you enter
    one function to create a summarizing matrix.

I mentioned a monkey wrench: We also want to sum only the
Amount values that are greater than or equal to $1,000. Only one record meets
this last condition: $1,010. That’s easy to see in this simple sheet, but in a
complex sheet with hundreds or thousands of records, you need to be confident
that your function’s working correctly.

There are two ways to accommodate this requirement. You
could hard-code the amount as an additional argument, but I recommend that you not do so unless you’re positive the
threshold amount will never change. Even when you’re positive, things change.
Once someone realizes how easy it was for you to work in this requirement,
they’ll ask for even more flexibility in this area.

In you want to hard-code the amount, use the following
function.

 =SUMIFS(Data!$C$5:$C$10,Data!$A$5:$A$10,Summary!B$4,Data!$B$5:$B$10,Summary!$A5,Data!$C$5:$C$10,">=1000")

I referenced the Amount field in Data as the new criteria
range and used “>=1000” as the new criteria value — a literal value
instead of a cell reference. A more flexible solution would be to reference a
cell that contains the new criteria.

 

=SUMIFS(Data!$C$5:$C$10,Data!$A$5:$A$10,Summary!B$4,Data!$B$5:$B$10,Summary!$A5,Data!$C$5:$C$10,$C$2)Figure C

 

It’s much easier to change the expression in C2 than it is
to update the function and recopy it. Below, I’ve changed the threshold to all
the values less than or equal to 1,000. All I did was replace the greater than
operator with the less than operator.

Figure D

 

Using the SUMIFS() function isn’t the only way to summarize
data sets with complex requirements. You can review Excel’s Subtotal and
Consolidate features in these TechRepublic articles:

Learn more about SUMIFS() by reading the TechRepublic article Use SUMIFS() to sum by multiple conditions in Excel.

Send me your question about Office

Please keep in mind that I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible: For instance, Please troubleshoot my workbook and fix what’s wrong probably won’t get a response, but Can you tell me why this formula isn’t returning the expected results? might. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.