Add cells including text

**HappyTrucker** · 08-14-2005, 06:19 AM

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** · 08-14-2005, 06:40 AM

try

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

for column A to J on row 1

Originally Posted by 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

**HappyTrucker** · 08-14-2005, 06:56 AM

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

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

**Bryan Hessey** · 08-14-2005, 07:28 AM

Thanks for the reply,

also possible is

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

to cover both types.

note, your formula tests row 2 and sums row 3, the recommended use of sumif (range to test, criteria, range to sum), mine is lazy and assumes the second range, however if you have consecutive rows of changing data be careful to check that the row format doesn't change and that the criteria is always in the same column for the tested and sum'd rows.

Cheers

Originally Posted by HappyTrucker

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

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

Bernard Liengme · 08-14-2005, 10:05 AM

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]...
>
> 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
>

**HappyTrucker** · 08-14-2005, 04:59 PM

Originally Posted by Bernard Liengme

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.

**Bryan Hessey** · 08-14-2005, 08:59 PM

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.

Originally Posted by HappyTrucker

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.