Google Sheets ArrayFormula Solution for Multiplying 2 Columns, One of Which Is a Column of Lists of Numbers

1
votes

I have 2 columns of inputs (A and B) and 1 column of output (C) with the number of rows unknown at design time. Each row’s inputs and outputs are independent; row 1 cannot affect row 2.

  • Column A consists of 2 possible types: a positive integer or a list of positive integers delimited by semicolons (“;”).
  • Column B consists of only a positive integer.
  • Column C is the total of column A multiplied by column B.

This is the basic formula I have devised for column C: =IF(NOT(ISBLANK($A2)),IF(ISNUMBER($B2),$A2*$B2,$B2*SUM(SPLIT($A2,"; ")))),""). And it works by filling the formula downwards, but the SUM being there makes it incompatible with ARRAYFORMULA. For robustness (because I cannot count on the users to autofill the cells themselves nor to avoid wreaking havoc on the formulae I’ve carefully laid down), I would like an ARRAYFORMULA-based solution which I can tuck inside of one protected cell in column C.

The current result using the non-ARRAYFORMULA solution:

Table of example inputs and outputs

The first 4 rows are just simple multiplication, which ARRAYFORMULA has no trouble doing. The last filled row represents (15+20+25)*1=60 which I could not do as an ARRAYFORMULA.

I have found one similar question which—unfortunately—does not apply to my case because the final solution does not use ARRAYFORMULA: sum comma delimited string of integers

1
arraysif-statementgoogle-sheetssumarray-formulas

1 Answers

1
votes

try:

=ARRAYFORMULA(IF((A2:A<>"")*(ISNUMBER(B2:B)), 
 MMULT(IFERROR(SPLIT(A2:A, ";")*1, 0), 
 ROW(INDIRECT("A1:A"&COLUMNS(IFERROR(SPLIT(A2:A, ";")*1, 0))))^0)*B2:B, ))

0