+ Reply to Thread
Results 1 to 7 of 7

Filter between two dates and return the sum

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Filter between two dates and return the sum

    Hi guys, I am wasting my time doing a job manually that is should be possible I would have thought to automate.
    However, my knowledge of Excel doesn't extend to this so would appreciate some advise if you can spare the time

    The data base:

    I have a spreadsheet that interrogates sage and returns a list of all the parts on all the purchase orders for 2012.

    This list includes parts complete and those yet to be worked on.

    There is a column H which shows the due date for the part as indicated in the attached image.

    There is also a column M as indicated that shows the total value of all the parts once they have been delivered to stores.

    Column M shows as a zero until the parts are received by our main stores.


    What do I need to achieve?

    As part of my KPI reporting I need to report on my weekly labour, my weekly throughput and running total of each.

    What am I doing at the moment?

    I am manually selecting all of the parts within the two dates relevant for the week number and noting the sum for that week in £'s
    I then put this into my spread sheet as the throughput for that week.

    This can take around 30 minutes as it is a large database.


    What do I need

    I have created a basic spreadsheet with a column that lists week 1 to week 52.

    I would like a formulae that I can put into the adjacent column.

    This formula would look at column H on the database and return the sum of all the values in Column M between two dates.

    The two dates for week 1 would be: 2nd January to 8th January for example

    I could put two columns at the side of the week number column if that helps for the start and finish date for each week.

    Sorry for the long post guys but hopefully if you haven't lost the will to live and topped yourself you can assist me with this

    Sincerely

    Martyn
    Attached Images Attached Images

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter between two dates and return the sum

    Hi Martyn.

    For what i can understand from your picture, you need SUMPRODUCT or SUMIFS(if you are in edition >2003).

    Would you like to upload a samall sample workbook? Not a picture.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Filter between two dates and return the sum

    No need for both dates - just list the start date against each week number (HINT: put the first date in B2, then in B3 put the formula =B2+7, format accordingly and copy down to where week 53 would be). Then in C2 you can have this formula:

    =SUMIF(Data!H:H,"<"&B3,Data!M:M) - SUMIF(Data!H:H,"<"&B2,Data!M:M)

    Then just copy this down as far as week 52.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Re: Filter between two dates and return the sum

    Quote Originally Posted by Pete_UK View Post
    No need for both dates - just list the start date against each week number (HINT: put the first date in B2, then in B3 put the formula =B2+7, format accordingly and copy down to where week 53 would be). Then in C2 you can have this formula:

    =SUMIF(Data!H:H,"<"&B3,Data!M:M) - SUMIF(Data!H:H,"<"&B2,Data!M:M)

    Then just copy this down as far as week 52.

    Hope this helps.

    Pete
    Yes it does, I'll give that a whirl tommorrow when I get back to work. But looking at it that will work like a charm.

    Thankyou very much sir.

    Just by way of an apology for the double post, I realised the attachment hadn't worked on the original post and couldn't seem to add it through editing which is down to my unfamiliarity with the attachment software.

    I then tried to delete the original and start again but found I can't delete my posts or if I could I couldn't see how to.

    In desperation, i then made the new post and appear to miffed quite a few people off.

    So sincere apologies for that and many thanks for help.

    Sincerely

    Martyn

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Filter between two dates and return the sum

    Quote Originally Posted by Marvlin View Post
    In desperation, i then made the new post and appear to miffed quite a few people off.
    I don't know if you can delete a post, but you could have gone back to the original and posted that it was a duplicate and that it should be ignored - perhaps with a link to your newer post. You will have gathered that it can be quite irritating to spend time on answering a post, only to find that someone else has also spent time on answering the duplicate.

    Anyway, if my suggestion works for you then come back and mark the thread as Solved (the FAQ gives details on how to do this).

    Pete

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Re: Filter between two dates and return the sum

    Solved but had to put actual date in and modify all 52 entries by hand, as shown below:

    =SUMIF('DATA'!I:I,"<22-1-2012",'DATA'!M:M)-SUMIF('DATA'!I:I,"<15-1-2012",'DATA'!M:M)

    For some reason using the cell locations wouldn't work.

    I have a working system now, so thanks for the help you gave me.

    Regards

    Martyn
    Attached Files Attached Files
    Last edited by Marvlin; 04-24-2012 at 10:17 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Filter between two dates and return the sum

    I suspect that those dates are not real dates - rather they are text values that happen to look like dates.

    Anyway, you have a solution, so that's the important thing.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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