Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. -- HappyTrucker ------------------------------------------------------------------------ HappyTrucker's Profile: http://www.excelforum.com/member.php...o&userid=25997 View this thread: http://www.excelforum.com/showthread...hreadid=395644 |
#4
|
|||
|
|||
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 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup on large text in cells | Excel Worksheet Functions | |||
Large amount of text in cells - Problem text.xls (0/1) | Excel Discussion (Misc queries) | |||
Format text across cells | New Users to Excel | |||
how do i add the same text after current text in multiple cells | Excel Discussion (Misc queries) | |||
how do I 'count' the number of cells with a text in red or black? | Excel Worksheet Functions |