Ah! I might have another idea … As I think I misunderstood your request a little (sorry )
The formula I gave you in my previous reply would return and sum all the amounts for the dates within the ranges you set up in your Planner
table without taking into account if the amount was paid or not …
So I guess, this might be where the issue is …
If you need to only sum the paid amounts from your Payables
table, the expression
thisRow.[Start Date] <= [Due Date] AND [Due Date] <= thisRow.[End Date]
… will not be enough in your Filter()
… You would also need to tell Filter()
to look for rows in your Payables
table where the Paid
checkbox is checked (i.e.: true
)
So the formula would be something like this:
Payables.Filter(
Paid
AND thisRow.[Start Date] <= [Due Date]
AND [Due Date] <= thisRow.[End Date]
).Amount.Sum()
This Filter()
formula will go through your Payables
table and return the rows where the checkbox Paid
is checked (a checkbox being a boolean and having only 2 viable states (true or false), you don’t need to precise the = true
here, it’s implied) and the Due Date
is within the range … All that’s left is to access the amounts of these rows and sum them
Now, if you need the opposite …
Payables.Filter(
Paid.Not()
AND thisRow.[Start Date] <= [Due Date]
AND [Due Date] <= thisRow.[End Date]
).Amount.Sum()
It will do same thing as the previous one when it comes to the range but it will only return (and sum) the amounts where the checkbox Paid
in your Payables
table is Paid.Not()
(in other words, Not() true, so false )
I’ve created a simplified version of your doc based on your screenshots so you could see the 3 different Filter()
formulas side by side …
The first one in the[All Amount in range]
is the sum of all the amounts within the date range regardless of the state of the Paid
checkbox in the Payables
table.
The second one, is the Paid
only (within the date range) and the last one is the not Paid
only (still within the date range)
I do not know that … But I can give you the list though …

= equal
 As in a = b:
a
is equal to b

!= not equal
 As in a != b:
a
is not equal to b

> (strictly) greater than
 As in a > b:
a
is strictly greater than b

< (strictly) less than
 As in a < b:
a
is strictly less than b

>= greater than or equal
 As in a >= b:
a
is greater than or equal to b

<= less than or equal
 As in a <= b:
a
is less than or equal to b
The magical autosuggester of Coda should help you with that too when you write certain formulas where you would need one of those …
(The autosuggester won’t necessarily be triggered in that fashion with dates as the autosuggester tends to suggest anything related to dates in this case )
I hope this helps