How to reference to a column sum in another table in the same doc?

Hello. I have multiple tables (Pack A, Pack B, Pack C etc.)
Each of those tables has HOURS column and at the bottom of that column there’s a sum of all the hours of the rows.

Apart from these, I have one other table called PACK Hours which has two columns: Pack Name, Total Hours. In each row of this table the cell under Total Hours column should show the sum of HOURS column in the table referenced in Pack Name column.

How can I achieve that ?

I couldn’t find such option in formulas

I think you’ve got a a few options here. One way is to create different columns for each pack and then sum it all together in the Total Hours column.

Another way is to use the switch if formula:

switchif(Packname=“Pack A”, [Pack A].[hours].sum(),
Packname=“Pack B”, [Pack B].[hours].sum(),
Packname=“Pack C”, [Pack C].[hours].sum())

And the best way may to combine all of the different packs into one large table and just label them in another column called Pack Name. Then you can do:

[All Packs].filter([Pack Name]=thisrow).[Hours].sum()

Hope this helps!

Hi @Seymur_Rahimov and Welcome to the Community :partying_face: !

If I understand your actual setup correctly (see the very first sample below :blush: ) you’ll need to use a SwitchIf() formula in your table Pack where you wish to Sum() the hours depending on the Pack in your field [Pack Name] such as :

SwitchIf(
  thisRow.Name ="Pack A", [Pack A].Hours.Sum(),
  thisRow.Name ="Pack B", [Pack B].Hours.Sum()
)

In other words this kind of says, if thisRow.Name ="Pack A" then, sum the values in the field hours for the table [Pack A], if thisRow.Name ="Pack B" then, sum the values in the field hours for the table [Pack B]

You’d need to do this for all the tables [Pack x] you have …

Now, another possibility which would require some modifications to your setup but maybe you could use one table to list the various packs and another table with the details for those packs …

The first table would be linked to the second one using a lookup field allowing you to select the concerned pack … All you would have to do to get the sum of the hours in the first table would be to gather back the rows from the second table and then sum the hours :blush:

Thanks @Pch ! Yes, you got it right in the first example.

Yes, switch option works, but sorry that I forgot to mention this: The number of Pack X tables is not known. And adding a new condition to the formula every time a new Pack X table is created is not comfortable. So, is there a way that I can get the name of table from the Name column of the current row ? I’ve tried [thisRow.Name].Hours but it doesn’t work

With the second suggestion I’ve made, even though I know you’d need to adapt your current doc, you really wouldn’t need to modify the SwitchIf() when adding a new pack :blush: (or even use SwitchIf() for that matter :innocent: ).

From the table “Pack details”, where each row would be linked to a pack from the “Summary” pack table, you could create filtered views (1 Pack, 1 view) and still use those views to add hours and other info … The sum would be automatically updated in the “Summary” pack table …

I’m sorry but what do you mean by that :innocent:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.