+ Reply to Thread
Results 1 to 11 of 11

=SumProducts() help please

  1. #1
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    20

    =SumProducts() help please

    Good Morning Everyone,
    I am having some difficulty with the SumProduct formula. I have attached a very basic dumb down version of my workbook so I do not confuse anyone trying to help or learn.

    The sample i have provide has two worksheets. "master" and "forecast". My goal here is to sum all the values on "forecast" in rows 4-8. Only if two dates for each column fall within the date range I entered on the sheet title Master. I have got this to work by using the following formula:

    =SUMPRODUCT((Forecast!$C$2:$S$2>=E$3)*(Forecast!$C$3:$S$3<=E$4),Forecast!$C4:$S4)

    My problem is that I cannot get formula to calculate properly between the date ranges if the date range I enter falls inside of the dates of each Job.

    To help explain further what I am trying to do. If you looks at "forecast" each job has a delivery date and a return date. On the sheet "Master" If I enter in Row 3 and row 4 of Column D through J dates I want to see what is still out on a job. If you see in Column G on the "Master" sheet my sums are not adding correctly. I have entered 1/1/15 -2/3/2015. The formula i am using is simply calculating everything that is out except the first job with returns on 2/5/2015. I need the formula to recognize that Job 1 is still out and need to be added to the rest of the counts even though I have asked to calculate from 1/1/2015 -2/3/2015. I apologize if I just confused everyone. And I greatly appreciate any help.

    Thank you,
    JBTest Page 3.xlsx
    Last edited by JB20; 11-02-2015 at 10:58 AM. Reason: removed password

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: =SumProducts() help please

    File is password protected!

  3. #3
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    20

    Re: =SumProducts() help please

    Sorry John. I'm slightly challenged. This should work.


    Test Page 3.xlsx

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: =SumProducts() help please

    I'm challenged too! I don't understand why Job 1 has to be included even though it isn't in your date range.Jobs 16 & 17 are also out of range but not to be included.

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: =SumProducts() help please

    ??? Shouldn't the formula be

    =SUMPRODUCT((Forecast!$C$2:$S$2>=D$3)*(Forecast!$C$3:$S$3<=$D$4),Forecast!$C4:$S4)
    Glenn



  6. #6
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    20

    Re: =SumProducts() help please

    Ignore 16 and 17 for now. Job 1 oes fall with in the criteria that i am trying to sum. the sum i need is a reflection of what is currently out on a job and not in the inventory. the products will continue to be counted as out until they return on the date entered on the forecast sheet. If a Job leaves on 1/1/2015 and is scheduled to return on 2/5/2015, then if I ask the cell to sum all jobs between 1/1/2015 - 2/3/2015. Any Job that is out between those dates need to counted for. In other words If a job delivers the 12/13/2014 but returns on 2/5/2015. That job should still be sum'd if i enter 1/1/2015 - 2/3/2015 given that this product is still out on a job. I hope this clears up my goal and didn't confuse everyone more.

  7. #7
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    20

    Re: =SumProducts() help please

    Quote Originally Posted by Glenn Kennedy View Post
    ??? Shouldn't the formula be

    =SUMPRODUCT((Forecast!$C$2:$S$2>=D$3)*(Forecast!$C$3:$S$3<=$D$4),Forecast!$C4:$S4)
    Hi Glen,
    Column D on "master" is simply showing me what is out from today until the end of the year. My concern is with Column G where I should be getting on master! D5 = 595 not 535. currently it is not calculating the "60" that is on Forecast! C4. which needs to be, because I want to calculate what is still out between the dates

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,003

    Re: =SumProducts() help please

    Could you input manually your desired values in sheet Master from cell D5 to J5?

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,306

    Re: =SumProducts() help please

    Not certain I understand the concept but this adds the 60 to column G. It also adds 60 to columns F and H. It also adds 35 to column I (15 and 20). This is an array-entered into D5 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

  10. #10
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    20

    Re: =SumProducts() help please

    Quote Originally Posted by bebo021999 View Post
    Could you input manually your desired values in sheet Master from cell D5 to J5?
    I am trying to eliminate the manual entry. The sheet i uploaded is just a small template of a workbook with 18 worksheets that all ultimately connect to the master sheet. also the list of jobs I have on the template is about an 1/8th of what is actually entered each month and all the dates vary. So you see I am trying to figure out a way to get excel to calculate this for me instead of spending hours upon hours each day manually doing it.

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,003

    Re: =SumProducts() help please

    Quote Originally Posted by JB20 View Post
    I am trying to eliminate the manual entry.
    Ha ha
    Just show your desired results, helpers will show you how to get them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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