Google Sheets ArrayFormula Solution for Multiplying 2 Columns, One of Which Is a Column of Lists of Numbers
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:
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
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, ))
- Remove first element from a $_POST array
- Trouble reading numbers from file into array
- Dynamically populate a subarray inside of an array declaration
- Only print array row data if id is found in a whitelist array
- Increment variable name in a loop
- Transpose an associative 2d array
- how to split each word in a string in a separate list
- Array is always empty even when it is expected to have elements
- Build a multi-line address string with conditional components
- Access only the first row of a 2d array
- Doubled elements in result array while scraping HTML content
- Loop over an array of unknown depth
- How to access keys and values of a multidimensional array
- How to union ranges in google spreadsheets
- Validate an array as "empty" if it has no elements or if all elements have no length
- Generate an array of href strings from all <a> tags in an HTML document using Simple HTML DOM Parser
- Extracting JSON response and putting them inside a customize JSON
- Best way to filter an objects's properties for data
- How to loop over json-encoded data?
- Fill array with numbers adding up to a nominated total without exceeding item maximum
- Generate an associative 2d array with incremented column values using a flat array as first level keys
- Put Values in Multidimensional Array keys
- What is the proper way to initialize a fixed length array?
- Need help understanding pointer to a type of n elements in C (int (*p)[10])?
- Create Pivot table and add additional columns from another dataframe
- Algorithm to reverse an array/string only in terms of rotate operations
- Group data from lines of a log file by one column and create subarrays from another column
- When did iterating on the span of an array become faster than iterating on the array itself
- Use values of a flat array as the keys in every row of a 2d array
- Function to clear any value by assigning to it a default constructed value of same type