Add cells including text

[HappyTrucker]

Hi,

I have a row of cells. as below:

Hol 8.00 9.75 9.00 Hol 8.00 10 9.25 9.00 8.25

I want to add the numerical elements in this row, but the text parts
are causing an error. Is there a forumula that will only add the
numerical parts, without resorting to nested IF statements saying
IF(cell="Hol") etc etc?

Thanks in advance

 

[Bryan Hessey]

try

=SUMIF(A1:J1,"<>Hol")

for column A to J on row 1

 

[HappyTrucker]

Thanks for that. Looks like I headed down the right lines while waitin
(can't stop trying can you?)

I did forget that the Hol can be 2 types, Statutory or Compulsor
Hol(s) or Hol(c). My fault.

I've been tinkering and came up with this, based on the contents of th
column headings DUTY and WTD(located over alternate columns) and th
value of the Constants sheet (Duty or WTD), depending on which I'
summing.:

Hol(s) 8.00 9.75 9.00 Hol(c) 0.00 10.00 9.25 9.00 8.25

=SUMIF(B2:M2,Constants!F2,B3:M3)

It seems to work too, which has surprised me most

Thanks again, at least I know I was using the right formula

 

[Bryan Hessey]

Thanks for the reply,

also possible is

=SUMIF(A1:J1,"<>Hol*")

to cover both types.

Cheers

 

[Bernard Liengme]

Why not =SUM(A1:J1) since SUM ignores text?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"HappyTrucker" <[email protected]>
wrote in message
news:[email protected]...

 

[HappyTrucker]

Why not =SUM(A1:J1) since SUM ignores text?

I can't use SUM unfortunately, because I needed to SUM alternat
columns B3,D3,F3 and C3,E3,G3 etc), not the whole row

 

[Bryan Hessey]

Hi,

Not sure of your data, but if it is in the format of your second post,
Hol(s) 8.00 9.75 9.00 Hol(c) 0.00 10.00 9.25 9.00 8.25
then that can be totalled using (your data in A8 to J8)

=IF(ISERROR(MATCH("Hol(c)",A8:J8,0)),SUM(A8:J8),SUM(A8:
(OFFSET(A8,0,L8,1,1))))
and in the next column
=IF(ISERROR(MATCH("Hol(c)",A8:J8,0)),"",SUM((OFFSET(A8,0,L8,1,1)
:J8)))

with, in column L
=MATCH("Hol(c)",A8:J8)-1

the first column will total up all hols if no Hol(c) exists, or the
hours to the left of that word.
the second column will total any hours after finding Hol(c) or be
blank.

Also, a sum can be =Sum(B8,D8,F8,H8) etc.

btw, well spotted Bernard, I read as far as "How do I sum excluding
some columns" and should have read on.
watch for spaces inserted to prevent smiley faces.