Add cells including text
 

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


--
HappyTrucker
------------------------------------------------------------------------
HappyTrucker's Profile: http://www.excelforum.com/member.php...o&userid=25997
View this thread: http://www.excelforum.com/showthread...hreadid=395644

try

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

for column A to J on row 1

HappyTrucker Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395644

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

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

I've been tinkering and came up with this, based on the contents of the
column headings DUTY and WTD(located over alternate columns) and the
value of the Constants sheet (Duty or WTD), depending on which I'm
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 :eek:

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


--
HappyTrucker
------------------------------------------------------------------------
HappyTrucker's Profile: http://www.excelforum.com/member.php...o&userid=25997
View this thread: http://www.excelforum.com/showthread...hreadid=395644

Thanks for the reply,

also possible is

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

to cover both types.

Cheers

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

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

I've been tinkering and came up with this, based on the contents of the
column headings DUTY and WTD(located over alternate columns) and the
value of the Constants sheet (Duty or WTD), depending on which I'm
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 :eek:

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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395644

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

"HappyTrucker"
wrote in message
news:HappyTrucker.1tr3uc_1124017510.4987@excelforu m-nospam.com...

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


--
HappyTrucker
------------------------------------------------------------------------
HappyTrucker's Profile:
http://www.excelforum.com/member.php...o&userid=25997
View this thread: http://www.excelforum.com/showthread...hreadid=395644

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


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


--
HappyTrucker
------------------------------------------------------------------------
HappyTrucker's Profile: http://www.excelforum.com/member.php...o&userid=25997
View this thread: http://www.excelforum.com/showthread...hreadid=395644

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),SU M(A8:
(OFFSET(A8,0,L8,1,1))))
and in the next column
=IF(ISERROR(MATCH("Hol(c)",A8:J8,0)),"",SUM((OFFSE T(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.


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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395644