I think this is difficult - can it be done?

[michael.beckinsale]

Hi All,

Sorry for the vague header but l couldn't figure out a sutiable title.

I would like to know if there is a way to do this:

T1.25
L2.00
T0.25
--------
3.00 Total of column ignoring letter prefixes

If it makes it easier the prefixes could be suffixes instead. Spaces
or characters such as : # could be used to seperate the letters from
the figures.

This is required for a 'Resource Planner' that l am developing that is
required to span as many days as possible accross the columns so using
a helper column is not a practical option.

Additionally l would like to apply group sub-totals (preferably using
data/subtotals) and still have the column summed ignoring the letters.

Has anyone got any ideas please?

TIA

Regards

Michael Beckinsale

 

[Bob Phillips]

=SUMPRODUCT(--(--RIGHT(A1:A3,LEN(A1:A3)-1)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

One way,

Using a prefix of # before your data you could achieve what you require with
text to columns by:-

Select the column
Data|text to columns

Select the delimeter # and then only import the number part which in effect
removes all the prefix data from the numbers and leaves the numbers in the
same column.

If the numbers are always a fixed width then you could use that instead of a
delimeter to decide on where to split the data.

Mike

 

[michael.beckinsale]

Hi Bob,

Many thanks, l didn't think of that! A difficult problem made easy
when you know how.

Any ideas on how that formula could be applied instead of the usual
sub-totals formula (preferable using data/subtotals) or by using VBA.
My main concern is performance but it is also important that the
subtotals are applied with grouping so that the + / - signs can be
used to expand & collapse.

The number of columns required is approx 270 and approx 500 rows with
100 subtotals. The subtotals will be conditionally formatted.

Mike - thanks for your input but l think you missed the bit about
'helper' colomns.

TIA

Regards

MB