[SUM IF CELL ABOVE EQUALS CELL BELOW]

Question

[SUM IF CELL ABOVE EQUALS CELL BELOW]. * link to the file https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit#gid=0 * in column f of the file I'd like to get the same result as shown in column d without having to drag the formula every time, I've tried turning it into an array, I've gone crazy, but with poor results suggestions? * * same thing with column g : in column g I'd like to get the same result as shown in column "e" without having to drag the formula every time, I've tried turning it into an array, I've gone crazy, but with poor results * THANK YOU THANK YOU

Translated with www.DeepL.com/Translator (free version)

Answer 1

Giovanni, delete everything from Columns D, E, F and G in the spreadsheet (including the headers). Also delete Rows 2 and 3, so that the data begins in Row 2.

Then place this formula in D2:

=ArrayFormula({"COL D HEADER";IF(A2:A="";;IF(COUNTIF(A2:A;A2:A)=1;B2:B;IF(COUNTIFS(A2:A;A2:A;ROW(A2:A);"<="&ROW(A2:A))=1;SUMIF(A2:A;A2:A;B2:B);0)))})

And place this formula in E2:

=ArrayFormula({"COL E HEADER";IF(A2:A="";;IF((COUNTIFS(A2:A;A2:A;ROW(A2:A);"<="&ROW(A2:A))=1)*(COUNTIFS(B2:B;B2:B;ROW(A2:A);"<="&ROW(A2:A))=1);VLOOKUP(A2:A&"~"&B2:B;QUERY({A2:A&"~"&B2:B\C2:C};"Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1");2;FALSE);IF((COUNTIFS(A2:A;A2:A;ROW(A2:A);"<="&ROW(A2:A))=1)+(COUNTIFS(B2:B;B2:B;ROW(A2:A);"<="&ROW(A2:A))=1);C2:C;0)))})

You may change the header text inside the formula.

En italiano

Giovanni, elimina tutto dalle colonne D, E, F e G nel foglio di calcolo (comprese le intestazioni). Elimina anche le righe 2 e 3, in modo che i dati inizino nella riga 2.

Poi inserisci questa formula nella D1:

=ArrayFormula({"COL D HEADER";SE(A2:A="";;SE(CONTA.SE(A2:A;A2:A)=1;B2:B;SE(CONTA.PIÙ.SE(A2:A;A2:A;RIF.RIGA(A2:A);"<="&RIF.RIGA(A2:A))=1;SOMMA.SE(A2:A;A2:A;B2:B);0)))})

E inserisci questa formula nella E1:

=ArrayFormula({"COL E HEADER";SE(A2:A="";;SE((CONTA.PIÙ.SE(A2:A;A2:A;RIF.RIGA(A2:A);"<="&RIF.RIGA(A2:A))=1)*(CONTA.PIÙ.SE(B2:B;B2:B;RIF.RIGA(A2:A);"<="&RIF.RIGA(A2:A))=1);CERCA.VERT(A2:A&"~"&B2:B;QUERY({A2:A&"~"&B2:B\C2:C};"Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1");2;FALSO);SE((CONTA.PIÙ.SE(A2:A;A2:A;RIF.RIGA(A2:A);"<="&RIF.RIGA(A2:A))=1)+(CONTA.PIÙ.SE(B2:B;B2:B;RIF.RIGA(A2:A);"<="&RIF.RIGA(A2:A))=1);C2:C;0)))})

È possibile modificare il testo dell'intestazione all'interno della formula.