Formula still sees filtered rows/data causing rolling sums to be incorrect

[DM7FXT]

When I filter out rows of data, my formula can still see the filtered data. This causes rolling sums to be incorrect. In Table 3, I have filtered out Red but the rolling sum formula still uses Red in its calculations. In this example, Cell E26 has the formula =(D26+E25) but I would like it to read =(D26+E24). Thank you for any help.

 

[mole999]

i think from 2013 AGGREGATE was included in excel, that will ignore filtered values

 

[jasonb75]

Try this in E24 and fill down

=AGGREGATE(9,5,D$24:D24)

@mole999 AGGREGATE is included from 2010 onwards.

 

[DM7FXT]

That is great, Aggregate worked a treat
Thank you both very much