SUM, OFFSET and non-contiguous ranges

[ShogunPatch]

I am trying to create a simple formula using the SUM function combined with OFFSET and named ranges as follows
=SUM(OFFSET([named range],,2)

This appears to work fine as long as my named ranges are contiguous, i.e. (B3:B5) for example; however, as soon as a named range contains non-contiguous cells, e.g. (B6,B9) I get a #VALUE! error.

I have searched online and found discussions on (and in some cases workaround solutions for) similar problems (from which I gather I simply have to accept that there are limitations to using OFFSET with non-contiguous ranges), but none that I have found deal specifically with my use of the (I would have thought relatively simple) SUM function so I was wondering whether anyone could suggest a simple solution or workaround.

I would attach a very simple sample worksheet demonstrating the problem if I could simply work out how to attach things to this post, but I can't, so hopefully the above narrative is sufficiently clear...

Many thanks.

 

[ShogunPatch]

In which case, how did you get on with my original suggestion in post 2?

Also, if you've got the right data, you could just us the in-built subtotal / pivot table features to sum up the revised calculations - how are the groupings determined?

The solution proposed in post 2 does successfully combine the SUM and OFFSET functions, but it does not use my already-named ranges which is the whole point of what I am trying to do, to avoid having to manually redefine all my SUM formulae. If I'm to manually redefine all the sums I might as well dispense with the OFFSET altogether and just type in

=SUM(D2,D4,D5)

I am trying to work out if there is a simple formula I can use which will (a) allow me to re-use my already named ranges and (b) can be written once and then copied down the entire column I need it in. So far it looks like there isn't though if anyone out there can show me otherwise I would be very grateful.

Using the built-in sub-total and pivot tables may be an option which I have already been trying to play about with. I'm pretty new to PVs and haven't been able to make it work yet.

 

[ShogunPatch]

I am now wondering whether a UDF might be the answer to my problems. By combining two different UDFs which I have found in other posts into a single formula I have achieved something close to what I want, but the problem is that the end result does not seem to update to reflect changes in the sheet. Following advice in yet other forums on the subject of UDFs not updating I have added "Application.Volatile" to both the UDFs in question but that doesn't seem to have had any effect. I don't know whether one or the other of the UDFs is specifically to blame, or whether it is the fact that they are being combined in a formula, but perhaps a single UDF combining what these two do and specifically written in such a way as to make it update every time the sheet changes (if such a thing is even possible?) might be a way forward?

Sadly, all of this is way above my current skill level but I can post details of the two UDFs in question if anyone might be prepared to help me with this?