+ Reply to Thread
Results 1 to 2 of 2

Sumproduct function where a range of column values are added prior to multiplication

  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Sumproduct function where a range of column values are added prior to multiplication

    I've successfully used SUMPRODUCT in the past to multiply values in two (or more) columns, and then add those values across multiple rows. What I'm trying to do this time is instead of multiplying by the value in a column, I'd like to multiply by the sum of several columns.

    I've attached a sample spreadsheet as an example.

    In Column A, I list the average lb/bushel of a given type of apple. Columns B-D contain the average "grading" of a given type of apple, and columns F-H contain the monthly sales of the apples. What I would like to do is, in a single formula,take the product of the lb/bushel (Column A) times the total January-March sales (Sum of Columns F-H) times the percentage of apples that are either grade A, B, or C (Sum of columns B-D, divided by 100 since it's a percentage), and then take the sum of this value for all rows. The answer for this is in H6, but in the spreadsheet where I'm trying to do this, there are too many rows to construct the function manually as I have in this example.

    What would be perfect is if there is a way to take the sum of a few columns and return an array with an equal number of rows but a single column where the value is the sum of the columns of a given row.

    I've tried using matrix and array functions to do this, but I'm not having much luck (though I suspect the answer lay somewhere with either of these type of functions). Does anyone have any idea if this is doable?

    Thanks!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-20-2012
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Sumproduct function where a range of column values are added prior to multiplication

    I've made some progress....

    First off, the answer in H6 is incorrect...there was an error in the formula where I was taking the sum across multiple rows, not just a single row. I've corrected this in the attached version.

    I've also found a formula that works, kinda.

    I'm entering the following as an array formula:

    =SUMPRODUCT($A$3:$A$5,MMULT($B$3:$D$5,TRANSPOSE(COLUMN($B$3:$D$5)^0))/100,MMULT(F3:H5,TRANSPOSE(COLUMN(F3:H5)^0)))

    It works, EXCEPT...

    If any of the cells in the arrays are empty, the function breaks down. If I can get it so that it interprets an empty cell as zero, I think I've got my answer.

    Help?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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