Software

December Office tip mailbag: a complex Excel SUMIFS() function

Susan Sales Harkins helps a reader hash out a SUMIFS() solution with several criteria values across multiple sheets and the obligatory monkey wrench.

 

Mail-envelope.svg.png
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.

 
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

 

OfficeDecMail_FigA_011014.JPG
 

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

 

OfficeDecMail_FigB_011014.JPG
 

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

 

OfficeDecMail_FigC_011014.JPG
 

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

 

OfficeDecMail_FigD_011014.JPG
 

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.

 

 

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
info
info

Hi


Well done as usual for flying the Excel flag high, but ...

1) Why use item data like 'C13', 'E14' etc. (confusing to most as it is row/col address terminology)

2) Why show people how to use clever but unnecessary (and complex) formulas when a simple PIVOT table would have achieved the same thing, provide hugely re-usable skills, look better, and be more flexible.


Greg

sparent
sparent

I've taken to using SUMIFS even when SUMIF will do. I find the former's syntax a lot easier than the latter's.

ssharkins
ssharkins

@info1.) This is how most people use Excel. What should I call cell C13 other than C13?  2.) Most users hate PivotTables. I know some people use them a lot, but the majority of every day users don't and they don't really want to. I am glad you mentioned them;  it will give readers other options to explore and that's always good.

Editor's Picks