Search code examples
google-sheetssumarray-formulasgoogle-sheets-querygs-vlookup

Suming in an Array Formula using date based VLOOKUPs

This is the sample file

I have two data sets.

  1. Cash balance recorded as a particular date
  2. Cash added to the till

Recorded Balance Cash Added

Problem To be able to know the theoritical cash blance by adding new "Cash Additions" to the last "Recorded Balance" in chronological order.

Expected outcome

The intent is to know theorical balance since the last recorded balance event.

I have tried to use Vlookup in Array Formulas for Dates

But got stuck with summing values from "Cash Additions" with an Array Formula

Any particular suggestions?

Solution

  • try:

    =ARRAYFORMULA(IF(A2:A="",,B2:B+IFNA(VLOOKUP(A2:A, 
 QUERY({IFNA(VLOOKUP('Cash Added'!A2:A, 
 SORT('Recorded Balance'!A2:A), 1, 1)), 'Cash Added'!B2:B}, 
 "select Col1,sum(Col2) 
  where Col2 is not null
  group by Col1
  label sum(Col2)''"), 2, 0))))

    0