how to sum two cells from different rows in filtered data

Dwise85

New Member
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 = )

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

alansidman

Well-known Member
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),
in
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
Maybe:

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

</tbody>
Sheet7

Worksheet Formulas
CellFormula
D1=A1+B2

</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))}

</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>