Google Sheets – Outputting Array Formula in One Row

filterformulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I came across the below array formula, which works really well, I'm just struggling to tweak it, and barely understand it's inner workings, but it's very good. Instead of displaying a total of stock left per line, I wondered if someone could help me keep it on one line? Example on Google link below.

Formula:

=ARRAYFORMULA({"";IF(B4:B&C4:C="",,MMULT((VLOOKUP(ROW(A4:A),FILTER({ROW(A4:A),A4:A},A4:A<>""),2,1)=TRANSPOSE(VLOOKUP(ROW(A4:A),FILTER({ROW(A4:A),A4:A},A4:A<>""),2,1)))*(ROW(A4:A)>=TRANSPOSE(ROW(A4:A))),B4:B-C4:C))})

Google link: https://docs.google.com/spreadsheets/d/1YqG3KwGzIJCiYwSBicfct8MSKSppUtE8RTB-6iY1C8Q/edit#gid=0

Best Answer

={""; ARRAYFORMULA(IF(B4:B<>"", IFERROR(VLOOKUP(A4:A, 
 QUERY(QUERY(QUERY(A4:C, 
 "select A,sum(B),sum(C) 
  where A is not null 
  group by A 
  label sum(B)'',sum(C)''", 0), 
 "select Col1,Col2-Col3 
  label Col2-Col3''", 0), 
 "where Col2 > 0", 0), 2, 0)), ))}

0

Related Topic