How to sum across tables

Question

I've got three sheets.&nbsp;Sheet1 has columns 'id' and 'hid.Sheet2 has columns 'id' and 'cnt'.Sheet3 has columns 'hid' and 'cnt'. The 'cnt' is calculated by adding the 'cnt' in Sheet2, where Sheet2.id = Sheet1.id and Sheet1.hid = Sheet3.hid.For example:idhid1111211131114222522262227222&nbsp;idcnt110220330440550660770&nbsp;hidcnt11160222220&nbsp;&nbsp;Can I use functions to get 'cnt' in Sheet 3?&nbsp;

riny_van_eekelen · Answer

wyboooo&nbsp;Can give you three solutions. And there are probably more. See attached.Power Query (A:B)Power Pivot (D:E)and dynamic array formulas (G:H)

harun24hr · Answer

wyboooo&nbsp;FILTER() with BYROW() then SUM() may work. See the attached file.=SUM(BYROW(FILTER(Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8=A2),LAMBDA(x,XLOOKUP(x,Sheet2!$A$2:$A$8,Sheet2!$B$2:$B$8,0,0))))

Forum Discussion

How to sum across tables

Share

Resources

id	hid
1	111
2	111
3	111
4	222
5	222
6	222
7	222

id	cnt
1	10
2	20
3	30
4	40
5	50
6	60
7	70

hid	cnt
111	60
222	220