how to sum two cells from different rows in filtered data

[Dwise85]

Hi Guys,

I have a formula adding (for example) a1+ b2, a2+b3, a3 +b4 ect ect.
A B
1 3 7
2 10 14
3 5 11
4 13 2
5 4 6


if I filter column A in the above example to cells less than "10' the example would look like this:
A B
1 3 7
3 5 11
5 4 6


what I am trying to do is sum first cell in row 1 column1 with row 2 column 2 in the filtered data.
so formula would calculate this:
a1+b3, a3 + B6 ect ect.

hopefully i have explained this clearly.

any help would be much appreciated = )

 

[alansidman]

Using Power Query here is the MCode which will solve your issue

//Import your table range as a table name Table2


let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [A] < 10),
    #"Inserted Addition" = Table.AddColumn(Table.SelectRows(Source, each [A] < 10), "Addition", each [A] + [B], type number)
in
    #"Inserted Addition"

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

A​

[/td][td]

B​

[/td][/tr]


[tr][td]

2​

[/td][td]

3​

[/td][td]

7​

[/td][/tr]


[tr][td]

3​

[/td][td]

10​

[/td][td]

14​

[/td][/tr]


[tr][td]

4​

[/td][td]

5​

[/td][td]

11​

[/td][/tr]


[tr][td]

5​

[/td][td]

13​

[/td][td]

2​

[/td][/tr]


[tr][td]

6​

[/td][td]

4​

[/td][td]

6​

[/td][/tr]
[/table]

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

A​

[/td][td]

B​

[/td][td]

Addition​

[/td][/tr]


[tr][td]

2​

[/td][td]

3​

[/td][td]

7​

[/td][td]

10​

[/td][/tr]


[tr][td]

3​

[/td][td]

5​

[/td][td]

11​

[/td][td]

16​

[/td][/tr]


[tr][td]

4​

[/td][td]

4​

[/td][td]

6​

[/td][td]

10​

[/td][/tr]
[/table]

 

[Eric W]

Maybe:

Excel 2012

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=A1+B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]{=A1+INDEX(B2:B$10,SMALL(IF(SUBTOTAL(102,OFFSET(B2,ROW(B2:B$10)-ROW(B2),0,1,1)),ROW(B2:B$10)-ROW(B2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]