+ Reply to Thread
Results 1 to 3 of 3

Sumproduct using an array create on the go...

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    69

    Sumproduct using an array create on the go...

    I'm trying to use the sumproduct function where one of the arrays are created on.

    The data in one column is like:
    4.40/4.75
    3.25/3.25
    5.60/5.70
    2.56/2.56
    6.04/6.04
    11.05/12.12
    8.00/8.92

    and in the other column
    1000
    525
    390
    450
    2500
    150
    660

    Sometimes I want the portion left from the "/" to be used in the sumproduct and sometimes I want the portion right from the "/" to be used.

    So the Sumproduct result for 1st case will be 31479.75 & for the 2nd case will be 32636.45.

    I know splitting the columns data in two using "/" as delimeter will do the job but I do not want to add any extra columns in sheet as it has a format fixed.

    Can anyone help? I'm using excel 2010

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Sumproduct using an array create on the go...

    Try

    =SUMPRODUCT(($B$2:$B$8)*--(LEFT($A$2:$A$8,FIND("/",$A$2:$A$8)-1)))

    and

    =SUMPRODUCT(($B$2:$B$8)*--(MID($A$2:$A$8,FIND("/",$A$2:$A$8)+1,6)))
    Last edited by JohnTopley; 09-01-2021 at 03:33 AM.

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    69

    Re: Sumproduct using an array create on the go...

    Thanks John

+ 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. Replies: 5
    Last Post: 02-22-2018, 01:47 AM
  2. [SOLVED] Help - Dynamically Create an Array, Based on Key Cell, and Count Text within Array
    By mattsmit87 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-30-2017, 03:44 PM
  3. SUMPRODUCT array formula doesn't recognize Array 5
    By trstew in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-08-2016, 05:01 PM
  4. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  5. [SOLVED] Create an array from "True" only then perform Sumproduct???
    By drew.j.harrison in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-14-2015, 10:59 PM
  6. Create an array based off values in another array - exclude blanks
    By clifton1230 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 05:35 PM
  7. sumproduct of a number array and a text array starting with 2 numbers
    By Bishonen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2013, 11:48 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