Susan Sales Harkins helps a reader hash out a SUMIFS() solution with several criteria values across multiple sheets and the obligatory monkey wrench.
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.
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.
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:
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.
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.
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.
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.
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:
- Display multiple subtotaling functions in Excel
- 10+ tips for working with Excel's Subtotal feature
- Use Consolidate to summarize Excel data without sorting
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 email@example.com.