SUMIFS function with calculated criteria

**RollsWRangler** · 06-24-2013, 10:08 AM

I'm trying to extend the range of the SUMIFS( ) function criteria field. I could type in "01/31/2013" which would evaluate to TRUE if one of the records in the criteria field was this exact date. What I want is a month-end date (using the eomonth function) so that any date in the range 01/01/2013 through 01/31/2013 would evaluate to 01/31/2013. What I do not want to do is to have to create a dalculate column in the data and use that as a criteria range. I want to use d variable parameter in one step via the SUMIFS( ) function.

**daddylonglegs** · 06-24-2013, 10:35 AM

To do that with SUMIFS you can specify the month start and end date, e.g. with dates in column A and amounts to sum in B try

=SUMIFS(B:B,A:A,">="&DATE(2013,1,1),A:A,"<="&DATE(2013,1,31)

**RollsWRangler** · 06-24-2013, 11:35 AM

Originally Posted by daddylonglegs

To do that with SUMIFS you can specify the month start and end date, e.g. with dates in column A and amounts to sum in B try

=SUMIFS(B:B,A:A,">="&DATE(2013,1,1),A:A,"<="&DATE(2013,1,31)

Thanks! I modified your answer slightly to accommodate the situation where I have in cells A1 through A12 date constants "01/31/2013" through "12/31/2013" and I want to write one formula to copy into B1:B12 as follows:

... 'data'!a1:a999,">"&eomonth(a1,-1),'data'!a1:a999,">="&eomonth(a1,0) ...

Now I can summarize invoices within multiple date ranges, which was where I was headed. Lesson learned 1) multiple criteria for one range, and 2) use ">=" inequality within quoted and append using ampersand to attach a cell reference (which may be complex).

SUMIFS function with calculated criteria

LinkBack

Thread Tools

Rate This Thread

Display

SUMIFS function with calculated criteria

Re: SUMIFS function with calculated criteria

Re: SUMIFS function with calculated criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions