Skip to main content

I’m looking to replicate a formula from Google Sheets in AirTable.



I have two tabs on a spreadsheet. One tab is a list of Editors (screenshot above) with the name of the editor in column A, an the amount owed in column B. The second tab is Customer Orders, which tracks each order, the editor assigned to the order, and the amount owed for completion of the order.


I’m trying to replicate the functionality such that the Editor tab sums up the amount owed for each editor. As I mark each payment on the Customer Orders tab complete with a “Y”, it is no longer counted toward the amount owed.


I’m having difficulty getting this functionality to work in AirTable. Thank you for your help!

You probably want a formula field (let’s call it Amount Outstanding) in the Customer Orders table that contains something like:


(I’m assuming you have a single select Order Paid field with “Yes” and “No” options, and an Order Amount field that contains the amount owed)


if( {Order Paid}=“Yes”, BLANK(), {Order Amount} )


You need a Link To Record field (that links to the Editor table) in the Customer Orders table. You can then assign an editor to each customer order.


Finally you need a Roll Up field in the Editors table. Choose the Amount Outstanding field in the Customer Orders table as the one to roll up. Put SUM(values) in the aggregation function box.

Great, thank you for your help. I’m updating my solution here for others’ future reference.


On the Orders table, I created an additional column to keep track of any amount that is not paid (value 240 below). Equation of IF(Paid=’’,Compensation), meaning if the advisor wasn’t paid for completing the order, then “Owed per Order” will tell me how much is outstanding. I manually update Compensation as the orders are completed (status column).



Then the separate Advisors table has a Rollup function where I just sum up the “Owed per Order” that’s in the Orders table (above)

Great, thank you for your help. I’m updating my solution here for others’ future reference.


On the Orders table, I created an additional column to keep track of any amount that is not paid (value 240 below). Equation of IF(Paid=’’,Compensation), meaning if the advisor wasn’t paid for completing the order, then “Owed per Order” will tell me how much is outstanding. I manually update Compensation as the orders are completed (status column).



Then the separate Advisors table has a Rollup function where I just sum up the “Owed per Order” that’s in the Orders table (above)


No problem. Just as an aside to help with your data integrity… I would strongly consider changing your Paid column to a single select column with “Yes” as an option. And would also change your Compensation column to a Currency (or, at least, Number) field.

No problem. Just as an aside to help with your data integrity… I would strongly consider changing your Paid column to a single select column with “Yes” as an option. And would also change your Compensation column to a Currency (or, at least, Number) field.



What about a Checkbox?


What about a Checkbox?


Yes - probably even better.

Done. Thank you both.

Reply


Terms & Conditions