1

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

1

={""; 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

  • Hi User0, thanks for the help, but this isn't what I'm looking for. Do you think I need to change the IF statement around? – Ceebee86 Jul 2 at 13:01
  • I have managed to do it with a singular formula on the google link in column F, and now struggling to get it in an array... – Ceebee86 Jul 2 at 14:38
  • no, literally just nothing, or rather " " in formula terms. – Ceebee86 Jul 2 at 15:06
  • @Ceebee86 answer updated – user0 Jul 2 at 15:14
  • 1
    Thanks, that's great. :) I modified it a little for my needs, but it does exactly what I was aiming for. :) – Ceebee86 Jul 3 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.