Nov 08 2022 12:03 AM
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:
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 |
Can I use functions to get 'cnt' in Sheet 3?
Nov 08 2022 12:28 AM
@wyboooo 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)
Nov 08 2022 12:50 AM
@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))))