Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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)

Posted on Apr 13, 2014 11:39 PM

Reply
2 replies

Apr 14, 2014 5:28 AM in response to Shifty Swifty

Here is an example that sums values that are in the current month:

User uploaded file


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)

Apr 14, 2014 12:40 PM in response to Shifty Swifty

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?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.