• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS with date ranges

W

wolfman

New Member
I am trying to incorporate a date range into my SUMIFS formula. I have a SUMIFS that is filtering by multiple criteria. I need to add the criteria that it should only grab data if in column "A" the date range is between 10/01/2012 - 10/31/2012 (this is just an example. I need to do this for all months, to get the sales figures for each month for several years back)
Any help will be appreciated.

Thank you for your time.
 
Hi ,

If in a different area of your workbook , you have the month numbers from 1 through 12 in the rows , and the years across the columns , you can have your SUMIFS formula as :

=SUMIFS(..... , column A , ">=" & DATE(year , monthnum , 1) , column A , "<=" & (DATE(year , monthnum+1,1)-1))

where ..... stands for the rest of your criteria.

The highlighted portion will take care of the date criteria.

Narayan
 
Thank you for the quick reply. I'll keep that method in mind, however it won't work for what I am doing.

The dates are in this format 2/23/2014, 3/15/2014, 12/1/2014, etc. There is one date in that format per row. I want to have a formula grab all the dates that are within a certain range in one column as one criteria in the SUMIFS function.
 
@wolfman

See the attached file. Sheet Source Data Cells I2:J4.

Edited: Removed the file as I think this is not your requirement.

Regards,
 
Last edited:
Can you clarify for below data, if I filter based on below data

Criteria1 - Red
Criteria2 - 1
Criteria3 - Tree
Criteria4 - Circle
Criteria5 - 1

I get a sum of 6,328 all have the same date.

What result do you expect?

Regards,
 
Excel Ninja,

I'm sorry I should have used the random feature to create my dates. I've updated the dates to illistrate my needs better.
Here are some examples of totals that I should be getting..

For Line 1: combination "Red, 1, Tree, Circle, 1" The total should be $452 for Jan. only. For all the other months having this same combination "Red, 1, Tree, Circle, 1" the result should be zero.
For Line 2: The Jan. total should be $936, Feb. $1638, Mar. $702. This is a good example to illustrate my needs. If the sumifs does not distinguish between the date ranges for each month then it will add all three totals together , and since each combination exists for each month the three numbers will be added together and shown on each line under each month in column H.
I think my explanation makes this sound much more complicated than it really is. All I really need is for the SUMIFS to filter based on all the criteria and then take a look at the month date range and say I want this, or I don't want this.
Here is my approximate guess at what this formula should look like : (See attached Spreadsheet)
If(H2="January"(SUM(SUMIFS('Source Data'!F:F,'Source Data'!A:A,Main!B2,'Source Data'!B:B,Main!C2,'Source Data'!C:C,Main!D2,'Source Data'!D:D,Main!E2,'Source Data'!E:E,Main!F2,'Source Data'!G:G,{equal to or greater than "41640" but less than or equal to "41670")If(H2="January"(SUM(SUMIFS('Source Data'!F:F,'Source Data'!A:A,Main!B2,'Source Data'!B:B,Main!C2,'Source Data'!C:C,Main!D2,'Source Data'!D:D,Main!E2,'Source Data'!E:E,Main!F2,'Source Data'!G:G,{equal to or greater than "41671" but less than or equal to "41698")If(H2="January"(SUM(SUMIFS('Source Data'!F:F,'Source Data'!A:A,Main!B2,'Source Data'!B:B,Main!C2,'Source Data'!C:C,Main!D2,'Source Data'!D:D,Main!E2,'Source Data'!E:E,Main!F2,'Source Data'!G:G,{equal to or greater than "41699" but less than or equal to "41729")
 

Attachments

Narayan,

The formula works great for one year at a time. Is there a way to make the formula sort by dates that cross into other years?

Thanks,

Jacob
 
Hi Jacob ,

At present you have in column H a month name , and so we are summing up over one month alone.

Under your new requirement , if the dates span more than one year , it would not matter as long as you do not have data for the same month in more than one year. Thus , you could have data from April 1 , 2014 till March 31 , 2015 and it could still be done.

However , if you have data for say 2 calendar years , from January 1 , 2013 till December 31 , 2014 , how would you go about summarising it ? What would your present Main tab look like then ?

Narayan
 
Naraya,

After creating muliple versions to illistrate what I am trying to do, I finally realized that I could explain better if I kept it very simple. Notice that on the attached spreadsheet each line would have different total if to SUMIFS were able to take into account month and year.

Thank you,

Jacob
 

Attachments

Narayan,

THANK YOU! You have saved me so much time. It works perfectly.

I'm sure there are many others out there who want to do this same thing. I hope they find this thread.

Vr,

Jacob
 
You must log in or register to reply here.
Back
Top