Sum two cells from two different tables

[magnus2]

Hi,


I'll need some help in Excel.
I have two tables with their own filtering/sort options for each columns.
Outside this I'll need to generate a a table with filtering/sort that can calculate the total amount from one ID in table1 + table2.
The issue is that whenever I filter either table1 or table2 the total table will not change respectively, it'll take the amount from the pre-selected cell in the function and not work dynamically.
See example below or download the example excel file.


Table 1:
ID | Amount
1 | 100
2 | 200
3 | 300


Table 2:
ID | Amount
1 | 10
2 | 20
3 | 30


Table total:
ID | Amount
1 | sum table1 amount from ID1 + table2 amount from ID1 = 110
2 | sum table1 amount from ID2 + table2 amount from ID2 = 220
3 | sum table1 amount from ID3 + table2 amount from ID3 = 330


Excel example file: https://drive.google.com/open?id=0B_Z-0hrw2O07VHJ1d2RsejZSMDQ

 

[magnus2]

Seems like I'm more of an beginner that I though on Excel. So I have extracted sheets from the actual workbook I'm trying to fix, just removed business essentials. Attached here: https://drive.google.com/open?id=0B_Z-0hrw2O07b2xhWFVLYVl0dGs


Link to other forum queries to avoid more work than needed
https://www.excelforum.com/excel-general/1202773-sum-two-cells-from-two-different-tables.html
https://www.mrexcel.com/forum/excel...o-cells-two-different-tables.html#post4918343
https://www.excelguru.ca/forums/sho...s-from-two-different-tables&p=34710#post34710

 

[istiasztalos]

Hi magnus2,

Nearly all Excel formulas ignore whether a row/cell or range in general is hidden or not, therefore are not affected by filtering at all.

To my knowledge there are only two formulas that can be used to make calculations (SUM, COUNT, AVG, etc...) on visible cells only, they are SUBTOTAL and AGGREGATE. Their usage should be quite straightforward, to sum up the column Amount, you can use =SUBTOTAL(9,table1[Amount])+SUBTOTAL(9,table2[Amount])

That is if you will manually filter for the IDs in the original tables. If you need to sum only selected ones then you'll need one more trick:
=SUMPRODUCT(--(criteria_range=criteria),SUBTOTAL(9,OFFSET(top_of_Amount,ROW(amount_column)-ROW(top_of_Amount),0)))

There's a detailed explanation here on how that works
https://exceljet.net/formula/count-visible-rows-only-with-criteria

Let me know if you have any issues.

Thanks!

 

[magnus2]

Solved with Sumif, thank you