HOWTO: Excel formula to add up all numbers from last month

So, suppose you have a spreadsheet with 2 columns: Date and Value. You would like to add all values that match a date from last month. The trick is that last month changes based on today's date.

So, to get this done, you need to create an array formula that would look like this:

=sum(if(month(date_range)=month(today()-1), value_range, 0))

An example, could look like so:

=sum(if(month(A2:A100)=month(today()-1), B2:B100, 0))

A thing to remember is that this is an Array Formula and in order for Excel or Google Docs acknowledge this, you have to commit it by pressing Ctrl + Shift + Enter

 

Comments