Formula help sum based on column match across 2 tables

1

Table 1: (2 columns)
Group, Details
1, xzy
2, pqr

Table 2: (3 columns)
Group Item Cost
1 a 10
1 b 20
2 l 90

Table 3 : (2 columns)
Group Total Cost
1 30
2 90

Note: “Group” in table 2 and 3 is referenced from table 1

I need a formula to match “group” in table 2 and 3 and add the cost and populate for that group row in total cost for all items in that group.

2

Welcome to community @Anupriya_Mouleesha

if I understood right, you want to see total in Table1 for each group. if so, you would add another column lets called it Total and set its formula to Table2.Filter(Group=thisRow).Cost.Sum() + Table3.Filter(Group=thisRow).Cost.Sum() - this should give you ((10+20) + 30) = 60 as a total next to Group 1 in Table1.

3

Nope, I meant I need the Total cost in Table 3 computed by formula. Table 1 is the master data table.

4

So something like if thisrow.name=table2.name, then sum of all item cost.

5

Ah! I got it… used your filter and did thisrow.group and it worked! phew! thanks!

1 Like