+ Reply to Thread
Results 1 to 9 of 9

Sumproduct if?

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    28

    Sumproduct if?

    Hi there,

    I have the following data in a tool I'm building (number of cases completed and average turnaround time for every day)

    Date Cases Completed Turnaround Time
    3/13/2017 165 10.05031447
    3/14/2017 182 9.350649351
    3/15/2017 71 10.3877551
    3/16/2017 215 8.701086957
    3/17/2017 157 9.614814815
    3/18/2017 19 9.294117647
    3/20/2017 210 10.5

    I would like to be able to see the weighted average turnaround days for the week selected by the user, i.e. the weighted average turnaround time for the week of March 13th-17th. There doesn't seem to be a SUMPRODUCTIF function...any ideas on what would work?

    Any help is much appreciated!!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sumproduct if?

    Does this do what you want?

    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A
    B
    C
    D
    1
    Date
    Cases
    Completed
    Turnaround Time
    2
    3/13/2017
    165
    10.05031447
    16.4225388
    3
    3/14/2017
    182
    9.350649351
    4
    3/15/2017
    71
    10.3877551
    5
    3/16/2017
    215
    8.701086957
    6
    3/17/2017
    157
    9.614814815
    7
    3/18/2017
    19
    9.294117647
    8
    3/20/2017
    210
    10.5
    Dave

  3. #3
    Registered User
    Join Date
    07-06-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    28

    Re: Sumproduct if?

    No, looks like it's incorrectly calculating. With a sumproduct the result would be 9.47:
    =SUMPRODUCT(B1:B5,C1:C5)/SUM(B1:B5)

  4. #4
    Registered User
    Join Date
    07-06-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    28

    Re: Sumproduct if?

    Help please!!

  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,604

    Re: Sumproduct if?

    Isn't the entry for 18th March in the same week as 13th - 17th March, or do you want to exclude weekend data?

    What enhancement to the formula that you gave in Post #3 do you want to see?

    Pete

  6. #6
    Registered User
    Join Date
    07-06-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    28

    Re: Sumproduct if?

    So the user has a drop down to determine what week they want to see (there's months worth of data, I just only posted a few lines above for the purpose of the example). In post #3 I specify what dates it needs to reference, but what I need is for it to know what dates to look up. It needs to only sumproduct the cells in columns B & C if they are in within the parameters.

    Let me know if I'm not making sense..

  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,604

    Re: Sumproduct if?

    Well, assume that the drop-down is in cell E1 and the user selects the week commencement date (i.e. Monday dates), then your formula would become:

    =SUMPRODUCT((A1:A31>=E1)*(A1:A31<E1+7),B1:B31,C1:C31)/SUMIFS(B1:B31,A1:A31,">="&E1,A1:A31,"<"&E1+7)

    assuming you have data for up to 31 days.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    07-06-2016
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    28

    Re: Sumproduct if?

    YES. Works perfectly.

    Thank you so much!

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

    Re: Sumproduct if?

    Glad to have helped.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Use sumproduct to further parse a sumproduct calculation
    By Araise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-19-2015, 08:17 PM
  2. Count SUMPRODUCT Members / Average of SUMPRODUCT
    By Shingaru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 03:59 PM
  3. Replies: 1
    Last Post: 05-19-2012, 02:54 AM
  4. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  5. Replies: 6
    Last Post: 03-09-2011, 08:01 AM
  6. Subtract Sumproduct from a sumproduct
    By Prcntrygrl in forum Excel General
    Replies: 6
    Last Post: 01-17-2011, 02:34 PM
  7. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM

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