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.

Back to Swimming

I got into the pool at my apartment after a gap of nearly 4 years. I can't imagine why the gap was so long, I guess it doesn't matter. I have my 3 year old daughter to thank as she was always curious about getting in the pool and kept pushing me. It was her first time and we had a ball. 

I'm reminded of this quote I heard from Robin Sharma - 

Children come to us more highly evolved than adults 

to teach us the lessons that we need to learn

Asset Allocation - What's yours?

If you read any personal finance or investment related book, you will invariably come across the term asset allocation and how it represents the single most important skill of any investor. Let me try to explain this term in a practical way.

If I gave you 1 lakh rupees, how would you invest it? 

• Would you invest the whole amount in a fixed deposit? 
• Would you max out your PPF account? (the new limit is 1.5 lakhs now)
• Use it to buy shares of your favorite stocks? 
• Invest it in mutual funds? If yes, which category? 
• Would you buy gold (physical or demat)?
• Invest it in real estate?

I've listed some of more popular investment vehicles, but there are many others available. 

The point is that if you try out this exercise by yourself, you might discover which asset classes you are more inclined to invest, based on your experience and understanding of that asset class. 

Observe your investment pattern. Do you split the amount into several categories or do you prefer to invest it in one or a few select categories?

Also know that the decision you take will depend on where you are in your financial life. If you are close to retirement (or financial freedom), you would prefer safer instruments like fixed deposits and bonds. If you have an inclination for the stock market, you might invest either directly in stocks or equity mutual funds.

Maintaining Consistency - Calm App

The key to being consistent is to have well-established routines.

I've been using the Calm app on Android for 6 months now. This app has a nice feature that tracks your meditation history and also tells you how long your streak is. Surprisingly, they don't advertise this feature on their site. 

Here's my meditation history for the first 3 months of this year. When I saw this yesterday, I was pleasantly surprised to see how consistent I was - 

The story for the next 2 months however was quite different - 

A famous research study conducted by the University College London states that it takes 66 days to form a new habit. In my case, I definitely crossed that threshold but I still ended up breaking the well-formed habit. So what's the deal here?

Well, I had to travel for 10 days in April, and that effectively disrupted my routine. Once I got back, I found it harder to settle back into my rhythm. This experience taught me 2 valuable lessons - 

(1) It may take 66 days to form a habit, but it takes a lot less time to break the habit (and I'm talking about good habits here!). So I need to be on my guard esp when I'm doing something that disrupts my routines (like travelling).

(2) Routines are key to forming and retaining habits. When it comes to meditation, doing the practice at the same time and at the same place works best.