How to sum across tables

Copper Contributor

I've got three sheets. 

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:

idhid
1111
2111
3111
4222
5222
6222
7222
 
idcnt
110
220
330
440
550
660
770
 
hidcnt
11160
222220

 

 

Can I use functions to get 'cnt' in Sheet 3?

 

2 Replies

@wyboooo Can give you three solutions. And there are probably more. See attached.

Riny_van_Eekelen_0-1667896004378.png

Power Query (A:B)

Power Pivot (D:E)

and dynamic array formulas (G:H)

@wyboooo 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))))