how to sum two cells from different rows in filtered data

Dwise85

New Member
Joined
Nov 16, 2018
Messages
17
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,099
Office Version
2019
Platform
Windows
Using Power Query here is the MCode which will solve your issue

Code:
//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
A
B
1
A​
B​
2
3​
7​
3
10​
14​
4
5​
11​
5
13​
2​
6
4​
6​

Data Range
A
B
C
1
A​
B​
Addition​
2
3​
7​
10​
3
5​
11​
16​
4
4​
6​
10​
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,177
Maybe:

Excel 2012
ABCDE
1371714
3511711
5464#NUM!
6#NUM!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet7

Worksheet Formulas
CellFormula
D1=A1+B2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
E1{=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))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Forum statistics

Threads
1,086,020
Messages
5,387,260
Members
402,054
Latest member
JHENGNDK

Some videos you may like

This Week's Hot Topics

Top