+ 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
    4,694

    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
    Yea though I walk through the valley of the shadow of death, at least it's a change of scenery

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

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

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

    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)

Similar Threads

  1. Index / Array Genius Needed
    By ryansm05 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-23-2018, 11:11 AM
  2. Excel Genius Needed - Reformatting Addresses
    By financeanalyst in forum Excel General
    Replies: 5
    Last Post: 10-11-2013, 02:37 PM
  3. [SOLVED] Genius Needed - Conditional Formatting.
    By themanwithnoshoes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2013, 08:14 AM
  4. genius needed asap!
    By jbaich in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2011, 06:41 PM
  5. Replies: 0
    Last Post: 01-26-2011, 05:18 PM
  6. VBA Brain Needed!
    By MAMC84 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2008, 12:21 PM
  7. VBA genius needed... Please help
    By bsnapool in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2006, 10:12 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