Sum a range of cells with without the first character "text" & "numerical"

[John]

Hi newsgroup,

I'm creating an absence calender, using the following key
H4 (hoilday 4 hours) S4.25 (sickness 4 1/4 hours).

Is there a function/ work around to summing a range of
cells that included text within the cell, ie just the
values?

Currenlty cell b8:al8 contains a named range "april" cell
AN8 is to be the total of all hours relating to H and
cell AO to be the total of S hours.

Not sure if this is the way to go. Many thanks

John

 

[Aladin Akyurek]

=COUNTIF(Range,"H4)*4

=COUNTIF(Range,"S4.25)*4.25

 

[John]

Thanks for the reply, however the problem is that I could
have any cobination for H or S from 0.01 or 7.25

Regards

John

 

[Biff]

Hi John!

For holidays (H):

=SUMPRODUCT(--(LEFT(april,1)="H"),--MID(april,2,255))

For sickness (S):

=SUMPRODUCT(--(LEFT(april,1)="S"),--MID(april,2,255))

Biff

 

[Aladin Akyurek]

For H-values...

=SUM(IF(ISNUMBER(--SUBSTITUTE(Range,"H","")),--SUBSTITUTE(Range,"H","")))

which must be confirmed with control+shift+enter instead of just with enter.

For S-values, replace "H" with "S".

 

[Ken Wright]

You've got answers to get round your problem, but you will probably find life
much easier if you use another column for the codes. If you do this you will be
able to use filters and pivot tables etc to give you much more analytical power
than you currently have.