+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT help needed to calculate rate (requires brain of a genius)

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    78

    Question SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    Please see attached workbook.

    This requires brain of a genius to solve. I need a formula for column L, so that I can get rid of the manual helper columns of D and E.

    Thank you so very much.
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    3,855

    Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    Perhaps this array formula:

    =SUMPRODUCT(IF($A$2:$A$19=$A$1:$A$18,IF($A$2:$A$19=$A$3:$A$20,$B$1:$B$18-$B$2:$B$19,$B$1:$B$18-I3),J3-$B$2:$B$19)*($A$2:$A$19=H3)*($C$2:$C$19))
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,252

    Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    similar variant, also requiring array entry:

    L3:
    =SUM(TEXT((IF(($B$1:$B$18<$J3)*($A$1:$A$18=$H3),$B$1:$B$18,$J3)-IF($B$2:$B$19>$I3,$B$2:$B$19,$I3))*($A$2:$A$19=$H3),"0;\0;0;")*($C$2:$C$19))
    confirmed with CTRL + SHIFT + ENTER
    copied down to L4 etc

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    SUMPRODUCT with CSE makes me want to scream...
    Click * below if this answer helped

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

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,765

    Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    ARRAY formula in M2 then copy down
    Please Login or Register  to view this content.
    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    78

    Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

    words cannot express how grateful to all of you.

    You guys are great! indeed genius

    Thank you so very much!

+ 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