Conditional sum between tables

[Fares_Alaboud]

Hi there,

Apologies in advance if my explanation isn’t great, but I will demonstrate what I need in an example:

I have two tables in one base. One is called Budget and the other is called Transactions.

In Budget, I store Category, and Current Spend.

In Transaction, I store Merchant, Category and Amount.

The field Category in Transaction is linked to the one in Budget (and by default, as a result I have another field in Budget called Transactions that shows me the list of all transactions that are tagged with a specific category).

I’d like Current Spend to show me the sum of all Amount fields from the table Transactions, but for each category.

So, if I have three rows in Transactions that say

Netflix, Subscriptions, $10
Spotify, Subscriptions, $10
Disneyland, Holiday Spend, $30

then for each category in the Budget table I’d like to see the respective sums in the Current Spend field (i.e. $20 for Subscriptions and $30 for Holiday Spend).

In terms of Excel, I’d like a SUMIF().

In terms of SQL, I’d like a SELECT SUM() FROM * WHERE *.

I’ve looked everywhere but can’t seem to find what I need.

Thank you so much in advance.

[AlliAlosa]

I think you can accomplish this with conditional roll-ups. On the “Transactions” table, add a formula field for each of your categories with the following statement:

IF({Category}="Subscriptions", {Amount}, 0)

You’ll of course want to replace the word “Subscriptions” for each of your categories.

Then on the “Budget” table, add a roll-up field for each category which sums the amount from the “Transactions” table.

I hope that helps!