The way I've achieved it, using Mario's model, is (in C5, and similarly in D5, E5, etc.):

{=SUMPRODUCT(MAX(IF(ISNUMBER(C$15:C$1285);C$15:C$1285(C$15:C$1285<=$BT$2)(C$15:C$1285>0)*(MOD(ROW(C$15:C$1285);5)=0));0))}

As an array formula, until SUMPRODUCT works on them, this returns an array of valid entries, from which MAX chooses the greatest.

Cell BT2 holds the date I enter to use as the last day of the month I want to restrict entries for.

I can thus get a snapshot in C5 of what the account balance was on the latest date in that range up to then - also in D5, E5, etc..

To explain the sections within the IF function for anyone who's interested in my version of Mario's answer:

C$15:C$1285 - this (in the array formula) returns the whole range of entries within which I'm searching.

ISNUMBER(C$15:C$1285) - checks that we're not looking at a text or blank entry.

. . . . If it were, then IF returns 0, the last number before the ))}. Otherwise...

(C$15:C$1285<=$BT$2) - this returns 1 (TRUE) for each cell in that range where it is no later than the cutoff date in BT2, else 0 (FALSE).

(C$15:C$1285>0) - this returns 1 (TRUE) for each cell >0, but 0 (FALSE) if the cell is zero or negative.

. . . . **and the master stroke:**

(MOD(ROW(C$15:C$1285);5)=0) - restricts the array results to every 5th cell in the range returned by the array formula, which hold dates

. . . . i.e. C15, C20, C25, etc.

MAX gets the latest of these array results,

and SUMPRODUCT means that for each cell the sections above are multiplied together and added.

Any FALSE test result 0 multiplied by the other sections will give 0, and hence exclude that cell from the array sum.

I don't understand why SUMPRODUCT and MAX aren't the other way round:

. . . . . . . . . . {=MAX(SUMPRODUCT(IF....

. . . . but I accept that when I tried it that way round it didn't work; I got #VALUE!

-1- The sheet seems to be badly designed (every-fifth-row-design).

-2- The subject and the text of the question are inconsistent.