I have a column like this:
A B C
1 Column
2 1
3 0
4 1
5 2
6 0
7 2
8 3
9 1
I want to be able to sum each pair of two rows with one or two formulas that I can drag down. So hard coded, my formulas would look like this:
A B C
1 Column
2 1 =SUM(A2:A3)
3 0 =SUM(A4:A5)
4 1 =SUM(A6:A7)
5 2 =SUM(A8:A9)
6 0
7 2
8 3
9 1
Thanks in advance.
Here is what I ended up doing. I added two reference columns then performed a SUMIFS() with another column matching every other reference column.
A B C D E
1 REFERENCE1 REFERENCE2 Column FORMULA REFMATCH
2 1 =ROUNDDOWN(A2) 1 =SUMIFS(C:C, B:B, E2) 1
3 1.5 =ROUNDDOWN(A3) 0 =SUMIFS(C:C, B:B, E3) 2
4 2 2 1 2 3
5 2.5 2 2 4 4
6 3 3 0
7 3.5 3 2
8 4 4 3
9 4.5 4 1
Try this:
=arrayformula( query( query( iferror( if( {1,1,0}, floor( mod(row(A:A)-{1,1},{10^99, 2}), {2,1} ), transpose( split( regexreplace( query( transpose( query( transpose(A2:A9 & char(9)), , 50000 ) ), , 50000 ), "\s+$", "" ), char(9) & " ", ) ) ) ), "select max(Col3) where Col3 is not null group by Col1 pivot Col2", 0 ), "select Col1 + Col2 offset 1 label Col1 + Col2 '' ", 0 ) )
This is an array formula that creates the whole result table in one go. It does not require helper columns.
Try this:
=ArrayFormula(ARRAY_CONSTRAIN(FILTER(A2:A,ISEVEN(ROW(A2:A))) + FILTER(A2:A,ISODD(ROW(A2:A))),ROUND(COUNTA(A2:A)/2),2))
This is an array formula, so it does not get dragged. That is, this one formula produces all results.
Simply put, this adds the values in all even rows to the values in all odd rows.
Since values are paired, ARRAY_CONSTRAIN just limits the return to half the rounded number of available values.
Could work adding a column "A" of pairs and putting this formula in the 3rd column =IF(A1=A2,"",SUMIF($A$1:$A$8,A1,$B$1:$B$8))
A B C
1 7 =IF(A1=A2,"",SUMIF($A$1:$A$8,A1,$B$1:$B$8))
1 8
2 9
2 34
3 2
3 4
4 5
4 6
drag down and should remain like this:
A B C
1 7
1 8 15
2 9
2 34 43
3 2
3 4 6
4 5
4 6 11
Isn't exactly what you want with just one formula, but it could work with one formula and one column added.