+ Reply to Thread
Results 1 to 10 of 10

Multiply 2 cells separated with comma

  1. #1
    Registered User
    Join Date
    07-14-2017
    Location
    malaysia
    MS-Off Ver
    2017
    Posts
    5

    Multiply 2 cells separated with comma

    Hi,
    Is there's any way to calculate 2 cells separate with comma,
    attached is the detail. thank you very much.

    Value1 are variable: 0,0,3,6,4,2,0,0,0,0
    Value2 are fix: 5,15,25,35,45,55,65,75,85,95

    i want to multiply those 2 cells:
    =SUM((0*5),(0*15),(3*25),(6*35),(4*45),(2*55),(0*65),(0*75),(0*85),(0*95))

    BR//
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Multiply 2 cells separated with comma

    Are there one-figures always in the first cell?

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Multiply 2 cells separated with comma

    if so
    =SUM(IFERROR(MID(A2,ROW(1:19),1)*ROW(1:19)*5,)) as array formula

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Multiply 2 cells separated with comma

    Expanding https://stackoverflow.com/questions/...-array-formula

    the following is an non array answer!

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-14-2017
    Location
    malaysia
    MS-Off Ver
    2017
    Posts
    5

    Re: Multiply 2 cells separated with comma

    Hi tim201110,
    Thank you very much for the feedback, ya thats for array formula.

    Hi davsth,
    thank a lot for your feedback / formula as per my expectation for non array.

    BR//

  6. #6
    Registered User
    Join Date
    07-14-2017
    Location
    malaysia
    MS-Off Ver
    2017
    Posts
    5

    Re: Multiply 2 cells separated with comma

    Dear all,
    once more,
    do you know formula if i want to sum value1
    sample
    Value1 are variable:
    0,0,3,6,4,2,0,0,0,0 --> 15
    0,0,3,6,4,2,0,0,8,0 --> 23

    BR//

  7. #7
    Registered User
    Join Date
    07-14-2017
    Location
    malaysia
    MS-Off Ver
    2017
    Posts
    5

    Re: Multiply 2 cells separated with comma

    sorry, just ignored my question.
    got it...
    =IF(ISBLANK(K3),0,SUMPRODUCT(MID(0&K3,LARGE(INDEX(ISNUMBER(--MID(K3,ROW(OFFSET(K3,,,LEN(K3)))-ROW(K3)+1,1))*(ROW(OFFSET(K3,,,LEN(K3)))-ROW(K3)+1),0),ROW(OFFSET(K3,,,LEN(K3)))-ROW(K3)+1)+1,1)*1))

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Multiply 2 cells separated with comma

    For SUM non-array regular formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 0,0,3,6,4,2,0,0,0,0 15
    2 0,0,3,6,4,2,0,0,8,0 23
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  9. #9
    Registered User
    Join Date
    07-14-2017
    Location
    malaysia
    MS-Off Ver
    2017
    Posts
    5

    Re: Multiply 2 cells separated with comma

    Hi Alkey,
    but seem still uncorrect if my variable value:
    14,1,0,0,0,0,0,0,0,0
    0,3,11,0,1,0,0,0,0,0
    15,0,0,0,0,0,0,0,0,0
    14,0,1,0,0,0,0,0,0,0

    BR//

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Multiply 2 cells separated with comma

    All your examples where for single digit separated by comma. For any other combination you would need an array formula

    **Must be entered with Ctrl+Shift+Enter key combination.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 0,0,3,6,4,2,0,0,0,0 15
    2 0,0,3,6,4,2,0,0,8,0 23
    3 14,1,0,0,0,0,0,0,0,0 15
    4 0,3,11,0,1,0,0,0,0,0 15
    5 15,0,0,0,0,0,0,0,0,0 15
    6 14,0,1,0,0,0,0,0,0,0 15
    If you want to multiply use this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 0,0,3,6,4,2,0,0,0,0 144
    2 0,0,3,6,4,2,0,0,8,0 1152
    3 14,1,0,0,0,0,0,0,0,0 14
    4 0,3,11,0,1,0,0,0,0,0 33
    5 15,0,0,0,0,0,0,0,0,0 15
    6 14,0,1,0,0,0,0,0,0,0 14
    Last edited by AlKey; 07-14-2017 at 01:13 PM.

+ 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] Showing many cells separated by comma in one cell
    By M Sleem in forum Excel General
    Replies: 3
    Last Post: 12-03-2015, 08:56 AM
  2. Data Validation (comma separated cells)
    By Hellix2 in forum Excel General
    Replies: 4
    Last Post: 03-06-2013, 02:26 AM
  3. Data Validation (comma separated cells)
    By Hellix2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2013, 08:18 PM
  4. List box data to cells separated by comma
    By naflas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2012, 12:44 PM
  5. Splitting comma separated cells
    By Dangermouse500 in forum Excel General
    Replies: 5
    Last Post: 02-20-2007, 01:29 AM
  6. [SOLVED] How do I merge the contents (separated by a comma) of 300+ cells?
    By elliott in forum Excel General
    Replies: 3
    Last Post: 05-10-2005, 04:06 PM
  7. Replies: 1
    Last Post: 02-07-2005, 02:06 PM

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