File attached.


Trial.xlsm

I am looking to SUM a range of cells along a row (G8 onwards). This row is intended to be a dynamic range (i.e. will grow), and will also be dynamically filtered (columns hidden based on user selections - not yet implemented - so have been hiding manually to test).

I have created a simpler version of this in row 18 (G onwards) where a SUBTOTAL is calculated and only for the visible rows (=SUBTOTAL(109,G19:G55) and this seems to work well.

I now want something slightly more elsewhere. I want a cell (Am currently trying to work with F9) to show the SUM of all the entries in Row G8 onwards but only for visible columns. I believe I have created a Dynamic Named range for G8 onwards called 'TotalHeadCount' and have then attempted to use the same SUBTOTAL formula as above but using the range as the 'TotalHeadCount' Named range. This doesn't seem to work however so I need of some help

Cheers