Current month in SUMIFS formula?
How can I use the current month (or previous month) as a condition in a SUMIFS formula?
Thanks!
OS X Mavericks (10.9.2)
How can I use the current month (or previous month) as a condition in a SUMIFS formula?
Thanks!
OS X Mavericks (10.9.2)
Here is an example that sums values that are in the current month:
B20=SUMIFS(B, A,"<="&EOMONTH(TODAY(),0), A, ">="&EDATE(EOMONTH(TODAY(),0)+"1d",−1))
this is shorthand for... select cell B20, then type (or copy and paste from here) the formula:
=SUMIFS(B, A,"<="&EOMONTH(TODAY(),0), A, ">="&EDATE(EOMONTH(TODAY(),0)+"1d",−1))
This formula totals values in column B when the date in column A meets both of the conditions:
<= end of this month
>= beginning of the this month
The end of this month is calculated as:
EOMONTH(TODAY(),0)
The beginning of this month is calculated as:
EDATE(EOMONTH(TODAY(),0)+"1d",−1)
SS,
An easy way to do this is to add a column, which may be hidden, in which you place an expression to extract the month corresponding to the date.
For instance, if the date is in Column A, you can add a Column B in which you write:
=MONTH(A)
April 14, 2014 will be represented as 4 in column B. At this point you can conditionally sum on there being a 4 in B.
Jerry
Current month in SUMIFS formula?