How to keep ongoing tally of visible cells

[honkin]

I have a sheet with around 172,000 rows and I need to keep a running total of profit. That part of it is normally no issue, but the sheet itself is to be used to develop profitable systems, so there will be quite a lot of filtering. When filtered, that running total of P&L gets broken.

Here it is working correctly in CG

Jumps Racing.xlsb
	CG	CH	CI	CJ
2	Total P&L	Peak P&L	Drawdown	Drawdown %
3	131.32
4	31.32
5	-68.68
6	-168.68
7	-268.68
8	-368.68
Sheet1

Cell Formulas
Range	Formula
CG3	CG3	=AD3
CG4:CG8	CG4	=CG3+AD4

So the above works fine with the standard running total calculation; In CG3 the formula is =AD3, then in CG4, it is =CG3+AD4 and so on down the column. When filtering, though, none of these formulas change, so any ongoing P&L will be inaccurate as you can see below

Jumps Racing.xlsb
	CG	CH	CI	CJ
1	WIN
2	Total P&L	Peak P&L	Drawdown	Drawdown %
3	131.32
7	-268.68
8	-368.68
Sheet1

Cell Formulas
Range	Formula
CG3	CG3	=AD3
CG7:CG8	CG7	=CG6+AD7

Individual profit per selection is in column AD and the running total will be in CG. So how is it possible for the first visible cell in CG to always equal the same cell in AD and the the P&L calculation proceeds as normal down the column of visible cells?

I guess the long and short of it is that any cell in CG will be equal to the same cell in AD + any visible cells in AD above the current row

Is that making sense at all?

cheers

 

[Toadstool]

Hi Honkin,

Try in CG3

=AGGREGATE(9,3,$AD$2:AD3)

 

[honkin]

Hi Honkin,

Try in CG3

=AGGREGATE(9,3,$AD$2:AD3)

That looks to have done it @Toadstool, so thanks very much. The downside is that with 172,000 rows, the autocalculate option makes the sheet constantly try to update, which takes quite some time. A shame, as I'd hoped this might be the perfect way to have the sheet be 100% dynamic with its calculations like drawndown, peak profit etc

cheers