+ Reply to Thread
Results 1 to 13 of 13

Calculating Sum of Column by Column

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    5

    Exclamation Calculating Sum of Column by Column

    Hi, I have no idea about excel formulas or programming whatsoever and I need your help guys.
    I am making an order for my shop and I want to multiply product price X (Column #1) with quantity Y (Column #2) and sum it. I have a picture attached to help you understand better what I want.
    uznSuuc.png
    I want it to be A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7=Z (Total)

    Thanks in advance guys!

    BTW My office version is 2010.
    Last edited by SilenceFiction; 07-03-2014 at 09:08 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculating Sum of Column by Column

    Hi,

    Look at the SUMPRODUCT function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I hope this helps

  3. #3
    Registered User
    Join Date
    07-02-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    5

    Re: Calculating Sum of Column by Column

    Quote Originally Posted by ajryan88 View Post
    Hi,

    Look at the SUMPRODUCT function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I hope this helps
    Sadly it doesn't, the output is "0".

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculating Sum of Column by Column

    Well the output of =SUMPRODUCT(A2:A7,B2:B7) should be A2*B2 + A3*B3 + ... + A7*B7 as required, by the definition of the SUMPRODUCT function.

    Can you please upload your workbook (Reply --> Go Advanced --> Manage Attachments).

    Thanks

  5. #5
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Calculating Sum of Column by Column

    It might be the settings. Some has ; instead of , as separator between arrays.
    But then you should have gotten an error message instead of a 0.

    I recommend you do as ajryan88 says and upload a workbook, easier to help you then.

  6. #6
    Registered User
    Join Date
    07-02-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    5

    Re: Calculating Sum of Column by Column

    Quote Originally Posted by ajryan88 View Post
    Well the output of =SUMPRODUCT(A2:A7,B2:B7) should be A2*B2 + A3*B3 + ... + A7*B7 as required, by the definition of the SUMPRODUCT function.

    Can you please upload your workbook (Reply --> Go Advanced --> Manage Attachments).

    Thanks
    Thank you
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,833

    Re: Calculating Sum of Column by Column

    Hi,

    Find the updated file.

    Punnam
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,833

    Re: Calculating Sum of Column by Column

    hi ,

    The problem is with "$" sign it is not necessary to insert "$" in cell for its representation it can done by changing the format .

    Punnam

  9. #9
    Registered User
    Join Date
    07-02-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    5

    Re: Calculating Sum of Column by Column

    Quote Originally Posted by Punnam View Post
    hi ,

    The problem is with "$" sign it is not necessary to insert "$" in cell for its representation it can done by changing the format .

    Punnam
    Is there a way of making it work with "$"? Can you guide me through to make it work with it?
    Thank you very much it does work without the "$", but it will be awesome if I could make it work with the "$" symbol.

  10. #10
    Registered User
    Join Date
    07-16-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Calculating Sum of Column by Column

    Mark the cells, right click choose format cells, choose custom in the menu to the left.
    In the type field write: "$"0.00
    Then ok.
    That should work

  11. #11
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,833

    Re: Calculating Sum of Column by Column

    hi SilenceFiction

    Need some clarification ,

    1) i have modified the format to meet u r requirement
    2) Is it mandatory for u enter the "$" sign in a cell ? or just under viewing i mean like printing and presentation.

    Punnam

  12. #12
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Calculating Sum of Column by Column

    ermm, in the cell you want the '$' is this just to show it's currency (as in you want a dollar value?)
    In which case, just format as Currency
    Pete
    Struggling Solutions Architect

  13. #13
    Registered User
    Join Date
    07-02-2014
    Location
    Israel
    MS-Off Ver
    2010
    Posts
    5

    Re: Calculating Sum of Column by Column

    Thank you all guys I managed to make it work

+ 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] Calculating average for one column based on value existing in another column
    By Leszczur in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2014, 10:34 AM
  2. Calculating Sum in one column, based on criteria in another column
    By Masonj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-31-2014, 12:29 AM
  3. calculating column 1 based on colum on column 4
    By leongandrew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2013, 10:09 AM
  4. Replies: 3
    Last Post: 03-18-2011, 11:28 AM
  5. [SOLVED] Calculating totals in a column based on a lookup in another column
    By Michael Wright via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2005, 05:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1