Search code examples
arraysgoogle-sheetssumvlookupgoogle-query-language

Using function as an arrayformula to calculate each row's data

What the function does is transpose a set row of ids and a changing row of quantities. In return the rows that have a quantity are returned with the id and the remaining array is used to query a pricing range. Finally the total price is summed for all the id's and their respective quantities. Also the final total price is multiplied by the corresponding row in CT.

Right now I have the function in every row in column CW4:CW as I cannot seem to get the arrayformula to calculate the data how I need it.

Spreadsheet (Hid unnecessary columns and pages): https://docs.google.com/spreadsheets/d/1E5p0WPQg6F8ZlBDWpKrJKHoXIlfQOOYYs7491Mr-EIA/edit?usp=sharing

Future apologies if my explanation isn't the greatest, it's late here and I've spent a deal of time stuck on the problem. Feel free to comment if you need clarification or an example sheet.

Function:

=ARRAYFORMULA(IFERROR(IFNA(
QUERY(
{VLOOKUP(QUERY({SPLIT(TRANSPOSE(CX$3:EO$3 & " " & CX4:EO4), " ", 1, 0)}, "SELECT Col1 WHERE Col2 IS NOT NULL", 0), EP$4:ER, 3) *
QUERY({SPLIT(TRANSPOSE(CX$3:EO$3 & " " & CX4:EO4), " ", 1, 0)}, "SELECT Col2 WHERE Col2 IS NOT NULL", 0)}, 
"SELECT sum(Col1) LABEL sum(Col1) ''", 0) / CT4, ""), ""
))

References I need to work as an array:

CX4:EO4
CT4

Pic of data:

Sheet stuffs

another one

Solution

  • try:

    =ARRAYFORMULA(IFERROR(MMULT(IFERROR(INDIRECT("I4:AZ"&MAX(ROW(A4:A)*(A4:A<>"")))*
 HLOOKUP(IF(INDIRECT("I4:AZ"&MAX(ROW(A4:A)*(A4:A<>"")))="",,I3:AZ3), 
 TRANSPOSE(BA:BC), 3, ), 0), SEQUENCE(COLUMNS(I:AZ), 1, 1, ))/E4:E))

    enter image description here

    demo spreadsheet

    or shorter:

    =INDEX(IFERROR(MMULT(IFNA(INDIRECT("I4:AZ"&MAX(ROW(A4:A)*(A4:A<>"")))*
 VLOOKUP(IF(INDIRECT("I4:AZ"&MAX(ROW(A4:A)*(A4:A<>"")))="",,I3:AZ3), 
 BA:BC, 3, ), 0), FLATTEN(COLUMN(I:AZ))^0)/E4:E))

    even more shorter but slow (not recommended to use):

    =INDEX(IFERROR(MMULT(IFNA(I4:AZ*VLOOKUP(IF(I4:AZ="",,I3:AZ3), 
 BA:BC, 3, ), 0), FLATTEN(COLUMN(I:AZ))^0)/E4:E))