SUMIFS equivalent for month & category data [Excel Tip]

I've been working on my excel version of an expense tracker and had this requirement of summing up all expenses under a certain category for a given month. Here's some sample data to illustrate my use case.

Input Table

This is where I enter my day-to-day expenses, mentioning the expense category.

Output Table

I'd like a summary of my expenses for each month, broken down by each category.

At first glance, this seems like a use case for the SUMIFS function since I have to specify multiple criteria. 

SUMIFS(sum_range, 

       criteria_range_1, criteria_1, 

       criteria_range_2, criteria_2, 

       ...)

Logically, this is what I would need to do - 

SUMIFS(<amounts column>, 

       <dates column>, <month filter>, 

       <category column>, <category filter>)

The trick is in comparing the dates column with the month filter. Since they are in different formats (DD-MMM-YY vs. MMM'YY), I need to first convert it to a common format for comparison. The TEXT function seems appropriate for this.

TEXT(value, format_text)

For example,

TEXT(B1, "mmyy") -- For Aug'14, this gives "0814"

TEXT(A2:A16, "mmyy") -- For the dates column, this gives an array of similar strings

Applying this to the SUMIFS function, 

SUMIFS(Input_Amt, 

       Input_Category, $A2, 

       TEXT(Input_Date, "mmyy"), TEXT(B1, "mmyy"))

I press CTRL+SHIFT, ENTER to indicate to Excel that this is an array formula. 

Unfortunately, this approach does not work. Excel just gives an error.

Not very helpful. 

Solution

After some web surfing, I found one possible way to solve this problem is to use the SUM function in a rather unique way. 

SUM(Input_Amt * 

    (TEXT(Input_Date,"mmyy")=TEXT(B$1,"mmyy")) * 

    (Input_Category=$A2))

Note the use of multiplication signs (*).

The above is entered as an array formula (CTRL+SHIFT, ENTER)

How does this work

I think the best way to understand this is by using the Evaluate Formula feature in Excel (under the Formula tab). This shows how the formula is evaluated step-by-step. 

Here's a summary of what it does -

• Convert each criteria into an array of {TRUE,FALSE} values
• Combine them to produce a single array of {TRUE,FALSE} values
• Multiply each row of the Amounts column against {TRUE,FALSE} array
• Multiplying with FALSE yields zero
• Multiplying with TRUE produces the same amount
• The remaining amounts are finally added together by the SUM function

Interestingly, this made more sense to me after I started taking the R Programming Course on Coursera. More on this in a later post. 

Download the Sample Sheet with solution.