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!
Bookmarks