+ Reply to Thread
Results 1 to 7 of 7

Problem on calculate sum of multiply if the data is N/A

  1. 07-14-2016, 10:43 PM #1
    kukarol
    kukarol is offline
    Registered User
    Join Date
    06-06-2016
    Location
    HK
    MS-Off Ver
    2007
    Posts
    6

    Problem on calculate sum of multiply if the data is N/A

    I have a problem on calculating the total score as attached image.

    I have data on B3:K1100, I need to calculate
    B3*C3+D3*E3+F3*G3+H3*I3+J3*K3 on L3
    B4*C4+D4*E4+F4*G4+H4*I4+J4*K4 on L4 etc......

    but sometimes E4 is N/A, then the formula will be
    B4*C4+F4*G4+H4*I4+J4*K4 on L4

    if E4, G4 and I4 is N/A then it will be
    B4*C4+J4*K4 on L4

    as I have many data on the sheet some data is exactly score "0", so that I cant change N/A to "0" in order to calculate it.
    please help and thank you for all your assistance.

    sample.jpg
    Reply With QuoteRegister To Reply
  2. 07-14-2016, 11:08 PM #2
    AlKey
    AlKey is offline
    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: Problem on calculate sum of multiply if the data is N/A

    Try this in L3 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by AlKey; 07-14-2016 at 11:10 PM.
    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
    Reply With QuoteRegister To Reply
  3. 07-15-2016, 12:43 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Problem on calculate sum of multiply if the data is N/A

    Hi, welcome to the forum

    1. upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    2. what is in the cells that show N/A - is it a formula, or an entry?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 07-15-2016, 01:17 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Problem on calculate sum of multiply if the data is N/A

    Array Formula - Requires CTRL+SHIFT+ENTER


    =SUM(IFERROR(B4:J4*C4:K4,0))


    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

    - Array Formula - Requires CTRL+SHIFT+ENTER


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  5. 07-15-2016, 02:33 AM #5
    kukarol
    kukarol is offline
    Registered User
    Join Date
    06-06-2016
    Location
    HK
    MS-Off Ver
    2007
    Posts
    6

    Re: Problem on calculate sum of multiply if the data is N/A

    Re Alkey: that's great! it works!! thank you so much.
    Re FDibbins: I will do so next times. thanks for your kind reminder.
    Re Sixthsense: that's not what I want.......but thanks for your answering.
    Reply With QuoteRegister To Reply
  6. 07-15-2016, 02:53 AM #6
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Problem on calculate sum of multiply if the data is N/A

    Quote Originally Posted by kukarol View Post
    Re Sixthsense: that's not what I want.......but thanks for your answering.
    I don't know what else is your expected solution ???
    Reply With QuoteRegister To Reply
  7. 07-15-2016, 04:00 AM #7
    kukarol
    kukarol is offline
    Registered User
    Join Date
    06-06-2016
    Location
    HK
    MS-Off Ver
    2007
    Posts
    6

    Re: Problem on calculate sum of multiply if the data is N/A

    Re Sixthsense: if I use " =SUM(IFERROR(B3:J3*C3:K3,0)) " then the calculation will be B3*C3+C3*D3+D3*E3+E3*F3+F3*G3+G3*H3+H3*I3+I3*J3 on L3 but what I need is B3*C3+D3*E3+F3*G3+H3*I3+J3*K3 on L3,
    the calculation formula is different, but using iferror is actually solved my problem. That's why the answer from Mr. AlKey is matched what I want. Hope this help for you. thanks a lot.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to calculate % then multiply by another cell
    By Rosanna Lam in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-05-2014, 03:49 PM
  2. [SOLVED] Excel 2007 : Multiply COUNTIF-NESTING FORMULAS PROBLEM
    By bobigoca in forum Excel General
    Replies: 7
    Last Post: 06-30-2012, 11:47 AM
  3. how to calculate difference of time from simple text, and add/multiply time.
    By MastahFez in forum Excel General
    Replies: 3
    Last Post: 03-02-2012, 08:18 PM
  4. Calculate combis and then multiply by cell values
    By faris al farik in forum Excel General
    Replies: 1
    Last Post: 01-28-2012, 06:17 PM
  5. Calculate total cost - multiply two values based on conditions in an array
    By CMDohm in forum Excel General
    Replies: 1
    Last Post: 07-06-2011, 10:53 AM
  6. Return values and calculate durations from a table of data - Interesting Problem!
    By rifitintaken in forum Excel General
    Replies: 0
    Last Post: 02-28-2010, 04:46 PM
  7. calculate time and multiply it by $ amount
    By DR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2006, 02:40 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1