# SUMIFS Help - Multiple date ranges

1. ## SUMIFS Help - Multiple date ranges

I'm trying to count some numbers if they are between multiple ranges of #s.

If in this example, count the #s that fall between the start and end dates, if they also fall in a calendar month a3 = 1/1/15, A4 = 2/1/15, A5 = 3/1/15, A6 = 4/1/15 and so on.

Start date = 11/15/14
End Date = 3/31/14
Count_Stuff = For the purposes of this example it's 72 things that are happening between the start and end dates above.

It seems to work until I hit the end date of 3/31/15.

=SUM(SUMIFS(Count_Stuff,Start_Date,"<="&A3,End_Date,">="&A4)) -- Then copy/drag down

Results:
1/1/2015 72
2/1/2015 72
3/1/2015 0
4/1/2015 0
5/1/2015 0

The results for 3/1 - 4/1 should also be 72 since the end date is between those ranges, but it's not working.

2. ## Re: SUMIFS Help - Multiple date ranges

I think it would be eaiser to help (and to understand) if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

3. ## Re: SUMIFS Help - Multiple date ranges

Book1.xlsxHere you go...

4. ## Re: SUMIFS Help - Multiple date ranges

hi dspblues ,

the data provided is not enough to explain your requirement ,

All the start date is 15-11-2014 & 31-03-2015
Your require date 01-01-2015 , or please explain it in more details

Punnam

5. ## Re: SUMIFS Help - Multiple date ranges

Hi,

You can use the formula in B24 and copy down, this will bring 72 for 3/1 but how are you saying that 4/1 should also get this as End date is 3/31/15.

=SUMPRODUCT((Start_Date<=A24)*(End_Date>=A24)*Count_Stuff)

6. ## Re: SUMIFS Help - Multiple date ranges

4/1 shouldn't get it.

This what I'm trying to do:

I'm going to have multiple monthly projects ruining that are 4-6 months in duration. On any given month, I need to count how many widgets are in flight.

7. ## Re: SUMIFS Help - Multiple date ranges

Originally Posted by misrasomendra
Hi,

You can use the formula in B24 and copy down, this will bring 72 for 3/1 but how are you saying that 4/1 should also get this as End date is 3/31/15.

=SUMPRODUCT((Start_Date<=A24)*(End_Date>=A24)*Count_Stuff)
This seems to work! Can you explain why? I'm not that familiar with sumproduct. Based on the description of what it does... it doesn't seem like it would. I also would have expected the end date to have to be >= 2/1/15.

8. ## Re: SUMIFS Help - Multiple date ranges

Originally Posted by dspblues
This seems to work! Can you explain why? I'm not that familiar with sumproduct. Based on the description of what it does... it doesn't seem like it would. I also would have expected the end date to have to be >= 2/1/15.
I did not understood "I also would have expected the end date to have to be >= 2/1/15" ????

You can modify your formula with the one below:

=SUMIFS(Count_Stuff,Start_Date,"<="&A24,End_Date,">="&A24)

9. ## Re: SUMIFS Help - Multiple date ranges

Can you explain how the formula works?

10. ## Re: SUMIFS Help - Multiple date ranges

First Remove SUM than see the reference changes I made to formula.

11. ## Re: SUMIFS Help - Multiple date ranges

I think the SUMIFS formula works... I thought I'd have to look at the end date if it was >= 2/1/15, but I guess not.

12. ## Re: SUMIFS Help - Multiple date ranges

I am not getting what are you looking for? Both my formula and your revised formula deliver the same results as you mentioned in your first post.

13. ## Re: SUMIFS Help - Multiple date ranges

I never said it didn't work. I asked you to please explain it.

14. ## Re: SUMIFS Help - Multiple date ranges

I think I tried that in my comment#10.

15. ## Re: SUMIFS Help - Multiple date ranges

I see my error, thanks for the help. I was close!

16. ## Re: SUMIFS Help - Multiple date ranges

Can you mark the thread as SOLVED now.

17. ## Re: SUMIFS Help - Multiple date ranges

I did, yesterday.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1