subtotal based on ref no

[Newbie]

Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want to
be able to create totals of variable ranges.
Each range changes based on the reference no.

Eg

Ref Desc Value
1.00 HOL <= = = this row is the header row and
will be bold
1.01 1st 10.00
1.14 2nd 25.00

SOME TEXT ON ROws which prevent me from using a Pivot table
2.00 WORK <= = = this is also a header row and will be
bold
2.14 wed 105.20
2.14.1 thurs 99.6


The desired result is
Ref Desc SubTotal
1.00 HOL 35.00
2.00 WORK 204.80

The subtotal needs to be for each change in whole number to the left of the
decimal point

Any ideas

Thanks

 

[Guest]

Hi,
If you can use another column, you can value the first digit, but this would
also assume that the number is only 1 digit. In column D try:
=VALUE(LEFT(A2,1)), which would give you a digit to subtotal on.

 

[Newbie]

Thanks but the value can be anything

Is there a way to only ref the whole number part i.e always round down in
VBA?

 

[Guest]

Hi Again,
Try this formula: =ROUNDDOWN(A2,0)

 

[Al Newbie]

thanks but rounddown is not available in vba

Hi Again,
Try this formula: =ROUNDDOWN(A2,0)