IF Between two dates - total of column in separate sheet

[Kristjan_Kristj]

I have two tables in Airtable. In one table I have a monthly cost field, and 3 date fields, (date of invoice, start period of invoice, end period of invoice). This table tells me how much a customer is paying per month, when they started receiving the service, for how many months they paid, and when the service period ended.

In a different table, I have a list of dates with monthly intervals (e.g., Jan 1st 2021, Feb 1st 2021 etc).

In that table I want to calculate how many customers in the first table were paying customers on each date, and how much they were paying in total MRR.

[Adam_Minich]

Hi @Kristjan_Kristjansso,

Adam from Airtable here.

My guess is that you can accomplish this by setting up conditional rollup and count fields. For the MRR you’d set up a conditional rollup that sums the values SUM(values) related to each record you’ve set up in the monthly intervals table. The condition you set would be to only sum values where the invoice was paid.

Calculating the number of paying customers per month is likely possible using a conditional count field. The same condition mentioned above should work here.

Let me know if this helps you solve it.

[Kristjan_Kristj]

Hey Adam, I don’t think that works since when I use the conditional rollup and count fields, I need to set a fixed date for those conditions, I would need to set that as a date within a field.

The only thing I have been able to come up with so far is using a script for it :confused: