Help with Use Case Involving a Parent-Child Reward System and Summing Rewards

Hi all,
I’m a bit stuck per the screenshots shown. I have basically created a reward system with my son under which he earns money but I cannot seem to be able to add the total from the two different reward amounts into the last table. I would like the last table to show the added rewards from the other two columns. Any help will be appreciated.

Thanks!



To add up all book reward amounts:

SUM(Book_Rewards.all.Reward_Amount)

To add up all earned amounts:

SUM(Book_Rewards.lookupRecords(Reward_Earned_=True).Reward_Amount)

or:

SUMPRODUCT(Book_Rewards.all.Reward_Amount, Book_Rewards.all.Reward_Earned_)

(this works because True=1 and False=0)

I suggest putting each type of reward in separate formula columns and then adding another formula which just adds them together, e.g. $Total_Book_Rewards + $Total_Morning_Rewards.

1 Like

Thanks so much for the proposed solutions. Unfortunately, they do not seem to be working. For instance, when I enter SUM(Book_Rewards.all.Reward_Amount) in a separate table, it returns a “0.” Please see the attached screenshot.


SUM skips over non-numeric values. It looks like the book reward amount column is Text (or Any) since it’s left-aligned. Try changing the column type to Numeric.

1 Like

Hi again,
Thanks so much for your prompt response and for helping me better understand Grist. I changed it to Numeric and it works! Thanks so much!

1 Like

Okay, so I have made a lot of progress; thank you. The last part that I am now facing an issue is when I type a formula to add the total book rewards and morning rewards. I get an error as shown. Anything I am likely doing wrong, please? Thanks!

It looks like you are editing the formula in a new empty table “Total_Rewards_Earned”, which only has columns A, B, C. The syntax like $Col_Name refers to the value from the named column from the current record. You could use lookups to bring in values from other tables, but it looks like it would be simpler for you to make a single table of totals, with three columns:

  • Total Book Rewards – as described in previous responses
  • Total Morning Rewards – similar
  • Other awards – this one could then use the formula like you tried:
    $Total_Book_Rewards - $Total_Morning_Rewards