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.

 

[PaddyD]

This sort of thing;

=SUM(N(OFFSET(INDIRECT({"B2","B4","B5"}),,2)))

 

[Aladin Akyurek]

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.

Care to clarify a bit more? Why not just

SUM(NonContigRange)

where NonContigRange consists of B6 and B9?

 

[ShogunPatch]

This sort of thing;

=SUM(N(OFFSET(INDIRECT({"B2","B4","B5"}),,2)))

paddyd thanks for your input. i'm not sure i understand it fully but i will try it in the morning when i am back at my pc and report back (i'm currently replying on my htc flyer).

 

[ShogunPatch]

Care to clarify a bit more? Why not just

SUM(NonContigRang
where NonContigRange consists of B6 and B9?

Aladin
yes of course that works and i already use that in my spreadsheet to sum up the original figures. the problem i have is that in addition to the original figures, against each line item and subtotal i do one of a range of calculations to produce an alternative to that number, which is then put in another column. whether the number in this new column is the unchanged original our the new number is determined by a yes/no switch against each line item. if the such is yes against any given subtotal then i need the amend figure to be used but if the such is no i need it to calculate the subtotal of the amended component parts.

did that make sense?

 

[Aladin Akyurek]

Aladin
yes of course that works and i already use that in my spreadsheet to sum up the original figures. the problem i have is that in addition to the original figures, against each line item and subtotal i do one of a range of calculations to produce an alternative to that number, which is then put in another column. whether the number in this new column is the unchanged original our the new number is determined by a yes/no switch against each line item. if the such is yes against any given subtotal then i need the amend figure to be used but if the such is no i need it to calculate the subtotal of the amended component parts.

did that make sense?

I'm afraid not...

Another idiom which works with a non-contiguous range consisting of single cells is:

=SUM(CHOOSE({1,2},B6,B9))

 

[ShogunPatch]

I'm afraid not...

Another idiom which works with a non-contiguous range consisting of single cells is:

=SUM(CHOOSE({1,2},B6,B9))

No, I thought it probably didn't... Thanks for bearing with me.

I don't really understand how I would apply your suggested solution above to my particular situation, because my non-contiguous ranges are named rather than explicitly listed.
Let me see if I can explain it any more clearly, hopefully the below screenshot will help:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Red	No
Green	yes
Blue	No
Subtotal1	No
Yellow	No
Black	yes
Subtotal2	No
Total	No
Notes:
Named Range "Red" =	B3
Named Range "Green" =	B4
Named Range "Blue" =	B5
Named Range "Subtotal1" =	B3:B5
Named Range "Yellow" =	B7
Named Range "Black" =	B8
Named Range "Subtotal2" =	B7:B8
Named Range "Total" =	B6,B9
Total formulae are e.g. =SUM(Total)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Original Values[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Revised Values[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Switch[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Selected Values[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Manually Summed[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Formula Sum[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"]54[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: center"]The revised[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Selects value[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Trying to create[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: center"]value may [/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]from Column B[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a formula[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: center"]reflect a range [/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]or D depending[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]summing the[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: center"]of calculations.[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]on Yes/No switch[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]offset named[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Here it is just[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]in Column E.[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ranges but does[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Column B x 10[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]As a result totals[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not work for[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: center"]to keep it[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]do not correctly[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]the range "Total"[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: center"]simple.[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]add up to sum [/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]because it is [/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]of the parts.[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]non-contiguous.[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=SUM(Subtotal1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=SUM(Subtotal2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]=SUM(Total)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=Red*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=Green*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=Blue*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=B5*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=Yellow*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=Black*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=B8*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=B9*10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(F2="Yes",D2,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=IF(F3="Yes",D3,B3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"]=IF(F4="Yes",D4,B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]=IF(F5="Yes",D5,B5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]=IF(F6="Yes",D6,B6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H7[/TH]
[TD="align: left"]=IF(F7="Yes",D7,B7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H8[/TH]
[TD="align: left"]=IF(F8="Yes",D8,B8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H9[/TH]
[TD="align: left"]=IF(F9="Yes",D9,B9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=H2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=H3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J4[/TH]
[TD="align: left"]=H4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J5[/TH]
[TD="align: left"]=SUM(J2:J4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J6[/TH]
[TD="align: left"]=H6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J7[/TH]
[TD="align: left"]=H7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J8[/TH]
[TD="align: left"]=SUM(J6:J7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J9[/TH]
[TD="align: left"]=SUM(J5,J8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=SUM(OFFSET(Red,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=SUM(OFFSET(Green,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L4[/TH]
[TD="align: left"]=SUM(OFFSET(Blue,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L5[/TH]
[TD="align: left"]=SUM(OFFSET(Subtotal1,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L6[/TH]
[TD="align: left"]=SUM(OFFSET(Yellow,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L7[/TH]
[TD="align: left"]=SUM(OFFSET(Black,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L8[/TH]
[TD="align: left"]=SUM(OFFSET(Subtotal2,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L9[/TH]
[TD="align: left"]=SUM(OFFSET(Total,,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A14[/TH]
[TD="align: left"]="Named Range """&A2&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A15[/TH]
[TD="align: left"]="Named Range """&A3&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A16[/TH]
[TD="align: left"]="Named Range """&A4&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A17[/TH]
[TD="align: left"]="Named Range """&A5&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A18[/TH]
[TD="align: left"]="Named Range """&A6&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A19[/TH]
[TD="align: left"]="Named Range """&A7&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A20[/TH]
[TD="align: left"]="Named Range """&A8&""" ="[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]="Named Range """&A9&""" ="[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Black[/TH]
[TD="align: left"]=Sheet1!$B$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Blue[/TH]
[TD="align: left"]=Sheet1!$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Green[/TH]
[TD="align: left"]=Sheet1!$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Red[/TH]
[TD="align: left"]=Sheet1!$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Subtotal1[/TH]
[TD="align: left"]=Sheet1!$B$2:$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Subtotal2[/TH]
[TD="align: left"]=Sheet1!$B$6:$B$7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Total[/TH]
[TD="align: left"]=Sheet1!$B$5,Sheet1!$B$8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Yellow[/TH]
[TD="align: left"]=Sheet1!$B$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The above is a small simplified example. Because my actual sheet has 1,000s of rows already (and may grow further), with a large number of sub-totals and totals dotted through the equivalent of Column B (all already named) I was hoping to devise a simple formula combining SUM and OFFSET to correctly calculate the totals. Column J shows the actual numbers I want but the formulae for all the totals have been manually setup (not practical in my actual worksheet); Column L shows my attempt at producing a formula to solve the problem, but as you can see it falls down as soon as a named range consists of non-contiguous cells.

I hope that clarifies things a bit and hope you will be able to point me in the direction of a workable solution.

 

[PaddyD]

rather than getting all complicated about it, why not just use an intermediary column that returns the values you're after and sum them?

=if(e2="Yes",d2,b2)

 

[ShogunPatch]

rather than getting all complicated about it, why not just use an intermediary column that returns the values you're after and sum them?

=if(e2="Yes",d2,b2)

Hi Paddy
Thanks for your suggestions but that is what I'm doing - see cell H2 - but that only makes sure the correct value is selected for individual line items, it doesn't ensure that the numbers add up correctly. As such it doesn't sort out the core issue of what I am trying to do, which is use existing defined but non-contiguous named ranges which are already used successfully to calculate sums in Column B as the structural basis for summing matching but horizontally offset ranges elsewhere in the sheet (i.e. the same row positions but in a different column). All of which means I will have to manually enter the formulae for the totals and sub-totals which is what I was trying to avoid.

 

[PaddyD]

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?