Excel Function: SUMIFS()

Well, all of you Excel fans know that historically, performing conditional sums that use more than one condition have been tricky, and required things like array formulas or the SUMPRODUCT function.  Excel 2007 users rejoice!  Microsoft Excel 2007 introduces: SUMIFS().  Bam!

If you'd like to play along, feel free to grab the Excel workbook from the download link below.

The SUMIFS() function, introduced in Excel 2007, handles conditional summing with multiple conditions. It is SUCH a handy function, I use it all the time.

One easy scenario is creating a summary report that is based on a data table. For example, you have a data table like the screenshot below, and need to create a simple summary report without using the robust Pivot Table feature.

Your report needs to sum the Amount for certain Reps and certain Items. The two conditions then are Rep and Item. That is where SUMIFS() comes in handy. The syntax for the function is as follows:

   =SUMIFS(SumRange, ConditionRange1, Criteria1, ConditionRange2, Criteria2,...)
Where:

• SumRange is the column that has the numbers you'd like to sum
• ConditionRange1 is the column that has the values you will compare
• Criteria1 is the value or cell reference to the criteria
• ... you can enter up to 127 number of ConditionRange/Criteria pairs

As you can see in the first row of the report below, the SUMIFS() function sums the total sales where Rep is A and Item is X100.

Another way we can use this function is to place sums into columns, like monthly columns.

See the report below for the example.

There is a little twist in the SUMIFS() function. It uses the EOMONTH() function and some concatenation.

The EOMONTH() function returns the last day of the month (End-Of-MONTH), and accepts two function arguments: any date and the number of months from the date. 

We need to concatenate the comparison operators ">=" and "<=" inside of the SUMIFS() function arguments so that the function will include all dates in the month.

Well, those are the basics, but you'll need to play with them more to become absolutely comfortable.  Good luck and enjoy this gift from Microsoft!