+ Reply to Thread
Results 1 to 12 of 12

Sum cells between worksheets with calculation

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Sum cells between worksheets with calculation

    I have a need to take a cell from one worksheet and multiply it with another cell on a second worksheet, and continue the same for the entire
    row / range of cells, then add them together for a total sum.

    SUM(Sheet1!A4*Sheet2!A2 + Sheet1!A5*Sheet2!A3 + Sheet1!A6*Sheet2!A4) - etc

    The above formula works, however when I attempt to put it in a range across the entire row, using " : ", it continues to give me an error, even as an array. Ideally I am would like to do SUM(Sheet1!A4*Sheet2!A2 : Sheet1!A6*Sheet2!A4) to pick up the entire row. Sum and Sumif seem to have a problem with the calculation.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum cells between worksheets with calculation

    Hi and welcome to the forum.

    Try:

    =SUMPRODUCT(Sheet1!A4:A6,Sheet2!A2:A4)

    Obviously amend the end row references here if you need to extend to further cells.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Sum cells between worksheets with calculation

    I get a #Value error and it won't accept the Ctrl/Shift Enter requirement for the array. If Sumproduct is the correct function to use, then I'll keep working on it. Many thanks for your help.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum cells between worksheets with calculation

    That formula does not require CTRL+SHIFT+ENTER.

    Can you tell me precisely what values were in each of the cells when you tried that formula?

    Regards

  5. #5
    Registered User
    Join Date
    07-27-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Sum cells between worksheets with calculation

    Sheet1 A4 = 4 (whole number)
    Sheet2 A2 = $40.00 - attempting to multiply the 4*40 = 160 for the result
    Sheet1 A5 = 1
    Sheet2 A3 = $20.00 1*20 = 20
    Sheet1 A6 = 2
    Sheet2 A4 = $10.00 2*10 = 20 - so my total result in the SUM should add up the 160+20+20, and give me 200

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum cells between worksheets with calculation

    And I presume your values with the "$" sign don't actually have that symbol in the cell, but are proper numbers just formatted as Currency?

    Of course, if those entries are actual text entries with the dollar sign as part of the string then this is more complicated.

    If you're not sure which is the case, if you test with the formula:

    =ISNUMBER(Sheet2!A2)

    then you will get TRUE if that cell contains a valid number (required for SUMPRODUCT), just formatted as Currency, and FALSE otherwise.

    If they are not valid numbers formatted as Currency, can I ask why not?

    Regards

  7. #7
    Registered User
    Join Date
    07-27-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Sum cells between worksheets with calculation

    Sheet 2 is formatted as currency, just using numbers in the input, Sheet 1 is just a general number, as it is a multiplier factor. I agree based on what I've also read and your help, sumproduct should work, and my rows & columns match between sheets. I'm going to move the data to a new xls to see if maybe this one could be corrupted or something else going on. Again, thank you .

  8. #8
    Registered User
    Join Date
    07-27-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Sum cells between worksheets with calculation

    Same error #value, thinks value used in formula wrong data type. I'll keep debugging.

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum cells between worksheets with calculation

    Can you not simply attach the workbook (desensitized if necessary)?

    Regards

  10. #10
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Sum cells between worksheets with calculation

    XOR solution is working fine

    see attached
    Attached Files Attached Files
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum cells between worksheets with calculation

    Thanks, majhu.

    Yes, that's what I get in my workbook, which is why I'm wondering what the OP can have done so as to get an error result.

    Regards

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum cells between worksheets with calculation

    Quote Originally Posted by adamss View Post
    I get a #Value error and it won't accept the Ctrl/Shift Enter requirement for the array. If Sumproduct is the correct function to use, then I'll keep working on it. Many thanks for your help.
    There are 2 possible reasons that you would get the #VALUE! error...

    1. There are already #VALUE! errors in the referenced ranges

    2. The referenced ranges are not exactly the same size
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Loop Import HTML Tables, Extract 3 Cells from Table, Do Calculation on Cells
    By jhowland in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-04-2013, 01:07 AM
  2. Replies: 3
    Last Post: 10-27-2008, 08:50 AM
  3. Conditional Calculation On Many Worksheets
    By Dave Preston in forum Excel General
    Replies: 2
    Last Post: 11-12-2007, 07:51 AM
  4. [SOLVED] 'Correct' way for multiple calculation from other worksheets?
    By Pheasant Plucker® in forum Excel General
    Replies: 1
    Last Post: 01-24-2006, 05:24 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