SUM a dynamic range not including hidden cells

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

Using the following to define the dynamic named range

=OFFSET(IAMT!$G$8,0,0,1,COUNTA(IAMT!$8:$8))

Not sure how to check the returned value

The SUBTOTAL function I am using seems to be returning the correct total value (i.e. if I add additional values then the subtotal is updated correctly its just the correction for hidden columns that doesn't appear to be working

Ah ok so the SUBTOTAL function is no good for this scenario. What can I use instead?

A combination of UDF and worksheet events given .Have a trail.
code for UDF

Please Login or Register  to view this content.