simplify fuction to sum part of cell

  • Thread starter Thread starter Steve Garman
  • Start date Start date
S

Steve Garman

I'm working with someone else's worksheet which is designed to be
pretty, not functional and I can't add helper columns (not even hidden
ones) as I'm not supposed to be messing with it.

The intention is that in Column B to H, an employee's hours for the day
should be entered. Optionally, this can be preceded by an indicator and
a slash e.g. E/9.5

The user is then supposed to *type* the total hours for the week into
column J ... That's annoying, to say the least.

If it wasn't for the optional indicator, I'd just put "=SUM(A2:G2)" into
I2 and copy it down.

Instead, I have a working formula which I suspect could be greatly
simplified. Can anyone give me an alternative that's a sensible length?

=IF(ISERROR(FIND("/",B5)),B5,MID(B5,(FIND("/",B5)+1),99))+
IF(ISERROR(FIND("/",C5)),C5,MID(C5,(FIND("/",C5)+1),99))+
IF(ISERROR(FIND("/",D5)),D5,MID(D5,(FIND("/",D5)+1),99))+
IF(ISERROR(FIND("/",E5)),E5,MID(E5,(FIND("/",E5)+1),99))+
IF(ISERROR(FIND("/",F5)),F5,MID(F5,(FIND("/",F5)+1),99))+
IF(ISERROR(FIND("/",G5)),G5,MID(G5,(FIND("/",G5)+1),99))+
IF(ISERROR(FIND("/",H5)),H5,MID(H5,(FIND("/",H5)+1),99))

BTW, the indicator length can vary from one to three characters
 
Here's my quick and dirty answer:

=SUM(VALUE(RIGHT(B1:H1,LEN(B1:H1)-FIND("/",B1:H1))))

Then press Ctrl+Shift+Enter to make it an array formula. This will place
curly brackets at the beginning and end of the formula (you cannot manually
type these in or the formula will not work).

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Many thanks for the swift reply.

The only problem with that is that some of the cells just contain
numbers, hence all the ISERROR checks in my original formula

But I really should be able to think my own way through it following
your suggestion.
 
Sorry, I completely missed the "optionally" phrasing in your original post.
Try the following instead:

=SUM(VALUE(RIGHT(B1:H1,LEN(B1:H1)-IF(ISERROR(FIND("/",B1:H1)),0,FIND("/",B1:
H1)))))

Again pressing Ctrl+Shift+Enter after entering it into the formula bar to
make it an array formula.

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
I'm working with someone else's worksheet which is designed to be
pretty, not functional and I can't add helper columns (not even hidden
ones) as I'm not supposed to be messing with it.
Click to expand...

So don't. Either copy their data and paste it as values into a blank worksheet
in a different workbook or enter simple formula references to the cells in the
other person's workbook. Then use all the ancillary columns and anything else
you need with your copy of the data.
The intention is that in Column B to H, an employee's hours for the day
should be entered. Optionally, this can be preceded by an indicator and
a slash e.g. E/9.5
Click to expand...

So you have either numbers or text strings. If you were using a copy of the
data, you could use Edit > Replace, replacing */ with nothing.
The user is then supposed to *type* the total hours for the week into
column J ... That's annoying, to say the least.
Click to expand...
...

Agreed. Stupid design.
Instead, I have a working formula which I suspect could be greatly
simplified. Can anyone give me an alternative that's a sensible length?

=IF(ISERROR(FIND("/",B5)),B5,MID(B5,(FIND("/",B5)+1),99))+
IF(ISERROR(FIND("/",C5)),C5,MID(C5,(FIND("/",C5)+1),99))+
IF(ISERROR(FIND("/",D5)),D5,MID(D5,(FIND("/",D5)+1),99))+
IF(ISERROR(FIND("/",E5)),E5,MID(E5,(FIND("/",E5)+1),99))+
IF(ISERROR(FIND("/",F5)),F5,MID(F5,(FIND("/",F5)+1),99))+
IF(ISERROR(FIND("/",G5)),G5,MID(G5,(FIND("/",G5)+1),99))+
IF(ISERROR(FIND("/",H5)),H5,MID(H5,(FIND("/",H5)+1),99))
Click to expand...

=SUMPRODUCT(IF(ISNUMBER(B5:H5),B5:H5,--MID(B5:H5,FIND("/",B5:H5)+1,99)))
 
Back
Top