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..

Improve this question
1

1 Answer 1

Reset to default
1

I believe what you're looking for is sumproduct.

In the totals cell, you would use

=sumproduct(multiplier_array, column_array)
Improve this answer
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, 2020 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, 2020 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, 2020 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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