SUMIFS Rock!

In the spring of 2013, we took a look at the remarkably potent SUMIFS function.  It drew so much interest, a review of this outstanding tool is in order.

Creating reports that go on and on over dozens of worksheets is one of the most Egregious Ruts that Excel users sometimes find themselves.   On the other hand, the use of Excel’s many fine tools (such as Sumproduct, DFunctions, or Vlookup) can result in highly effective and professional Interactive Reports.  These reports are much more versatile and tend to be operated with more efficacy by the end user.

One often overlooked, but ultimately powerful and easy to use function, is SUMIFS.  Unlike its little cousin, SUMIF, the SUMIFS function allows you to conditionally sum values with Multiple Conditions, rather than just one.  This, of course, makes it a noble choice for interactive report.

The Syntax for SUMIFS is as follows:

SUMIFS(SumRange, [CriteriaRange1, Criteria1], [CriteriaRange2, Criteria2], …)

Let’s look at an example:  Assume that you have a table/database that has multiple entries of Sales for Months and Weeks. Using a SUMIFS formula, along with dropdown boxes for the Month and Week, you can make an Interactive Report that is contingent on the parameters you choose. Note that the Amount in the report on the right of the illustration below Sums up any value which has September for the month and Week 1 for the week.  Pretty Cool!

SUMIFS; Another powerful Excel tool that can make you look like a Rockstar!  Give it a try and find out what you might have been missing.