Sum values of column of products based on key

[Jimmy_Boggs]

I am trying to sum the value of products in an inline form. I’m not sure that the way I am doing it is best practice so I’m open to any suggestions, but this is based on an example I’ve found online. I have included some screenshots with annotation which will hopefully clarify the issue.

I’ve created a new product named “Total” then added a virtual column to the form’s table that contains this formula:

IF([Product]="Total",SUM(SELECT(Tally_Details[Scale],[Product]<>"Total")),[Scale]).

Tally_Details[Scale] being the column of values I am trying to sum.

The last product entered on the form would be “Total”, which uses the virtual column to sum all the products within a tally. This almost works but the problem is that my current formula sums ALL the products in ALL of the forms. I only want to sum products that are grouped based on the [Tally_Key] column, Please see screenshot for clarification.

[Steve]

This might be a problem:

Jimmy_Boggs:

[Product]<>“Total”

Read more:

https://community.appsheet.com/search?q=@steve%20quirk

[Jimmy_Boggs]

Thanks for pointing that out. I think I fixed this correctly to avoid any quirks.

IF([Product]="TOTAL",SUM(SELECT(Tally_Details[Scale], (IN([Product], {"TOTAL"})))),[Scale])

I’m still facing the problem of having all my products summed together and am not sure how to only sum them based on having the same [Tally_Key]. Any ideas?

[Steve · Accepted solution]

IF(
  ("TOTAL" = [Product]),
  SUM(
    SELECT(
      Tally_Details[Scale],
      AND(
        ISNOTBLANK([Tally_Key]),
        ([Tally_Key] = [_THISROW].[Tally_Key]),
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
      )
    )
  ),
  [Scale]
)