I have a number of invoices:

Where the invoices

• are not necessarily sorted by start and end date
• are not necessarily sorted by paid date.
• might not have a paid-on value.

I want to add a field called paid to date that would show me the amount I have been paid so far where it would add the amount for:

• the current invoice
• the invoices that were paid prior to this invoice's paid-on date.
• the invoices with the same paid-on date as this invoice but with a start date <= this invoice's start date.

Effectively mirroring the hard-coded column.

This is how I do it with a query (which might not be the simplest/most elegant way of doing it)

=Index(
    query(
      A1:E10, 
      "select SUM(E)
      where D is not null
      and (
        D < date '"& TEXT( D2,"yyyy-mm-dd")&"' 
        OR (
          D = date '"& TEXT( D2,"yyyy-mm-dd")&"'
          and
          B <= date '"& TEXT( B2,"yyyy-mm-dd")&"'
        )
      )"
    ),
    2, 1
  )

which is all well and good. but I want to be able to do it with array-formula, so I can have it auto-generated for me.

I tried using it inside array-formula but the value is only ever generated for the first row. I guess it's misinterpreting the range I am passing as the range of the query function, ie A1:E10. is there an easy way of fixing it?

Do I need to use VLookup?

CodePudding user response：

If you have Excel 365, you can Filter directly.
Note that I am using a table with structured references. Then you don't need to adjust the range references as you add/remove rows from the table.

=SUM(FILTER([amount],([paid on]<[@[paid on]]) (([paid on]=[@[paid on]])*([start]<[@start])),0),[@amount])

This part does the filtering:

...([paid on]<[@[paid on]]) (([paid on]=[@[paid on]])*([start]<[@start]))...