0

How would I go about formatting an arrayformula for this?:

=$D10*(sum($F10:$I10))

I've tried a few different ways but none of them work. I have a bunch of rows with that formula (where of course the row number matches, so for example:

=$D10*(sum($F10:$I10))
=$D11*(sum($F11:$I11))
=$D12*(sum($F12:$I12)) etc...

I need this formula in each row but I'm trying to figure out an arrayformula so that it works when I add or subtract rows.

Thanks for your help!

UPDATE************************************************************************

I've just figured out that =arrayformula(D7:D*(F7:F+G7:G+H7:H+I7:I)) works but I might need to add and subtract columns too. Is there a way to make it work with sum()?

Shoelaced
  • 846
  • 5
  • 27

2 Answers2

1

I believe MMULT can be a good alternative:

=ArrayFormula(if(len(D2:D), D2:D*mmult(N(F2:I),transpose(column(F2:I2)^0)),))

Change ranges to suit.

JPV
  • 26,499
  • 4
  • 33
  • 48
0

The best way to solve math problem is to split it.

You have two multipliers: D x sum(F:F)

The first task is to make ArrayFormula with D. It's simple:

=ArraFormula(D10:D1000)

And the hard part is to make ArrayFormula with sum. This part was already asked and answered here by AdamL. In your case:

=ArrayFormula(SUMIF(IF(COLUMN(F1:I1),ROW(A10:A1000)),ROW(A10:A1000),F10:I1000))

And your final formula is

=ArrayFormula(D10:D1000 * 
SUMIF(IF(COLUMN(F1:I1),ROW(A10:A1000)),ROW(A10:A1000),F10:I1000))
Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81