1

I'm trying to apply multipliers to rows within a column total, having issues.. I've tried multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck..

Search attempts included (via both Google and Bing) "Google Sheets - Multiply Rows/Columns By Given Cell" in a variety of wordings; to which the most relevant result has always been "How to Multiply Numbers in Google Sheets"

I have a table that looks like this:

Totals      =            =        5 =        1
==============================================
Multiplier  =  Row Label = Column 1 = Column 2
==============================================
         0  =            =        1          0
         1  =            =        1          1
         2  =            =        2          0

As you can see in the example, I've applied the multipliers to demonstrate what I'm looking for.
What I'm looking for is for the "Totals" Row to reflect the "Multiplier" associated with the given column and row..

If possible, I'm trying to do this without applying any custom functions..

1
1

I believe what you're looking for is sumproduct.

In the totals cell, you would use

=sumproduct(multiplier_array, column_array)
3
  • Perfect, thank you. As an afterthought, is there a way to make it so that this changes negative values to 0 so that a negative won't effect the endline? – Wayne Jul 2 '20 at 20:27
  • If you want to change negative values to 0, I would clean up the data first. To do this I would create a new column (let's call it column 3) for the cleaned up data and use the max function. In column 3 I would use the following formula: =max(column_1_cell_reference, 0) This will give you the cleaned up data in column 3 (where negative values are turned into 0 values). – Dave Drysdale Jul 2 '20 at 23:23
  • It turned out that applying MAX(0, range) on both the multiplier and the data column seemed to have worked. And the data is constantly changing so a cleanup wasn't an ideal solution. I do very much appreciate your assistance though, tyvm. – Wayne Jul 3 '20 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.