sum text&numbers

P

puiuluipui

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 
R

Rick Rothstein

To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss
 
S

Shane Devenshire

Hi,

Try this:

=SUMPRODUCT(--SUBSTITUTE(A1:A2,"late ",""))

assuming your two enteries are in A1:A2. On the plus side of this fomula is
that is will work for 100 cells just as well as 2. Format the cell to time.
 
P

puiuluipui

Thanks!

"Rick Rothstein" a scris:
To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss

--
Rick (MVP - Excel)


puiuluipui said:
Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 
P

puiuluipui

Thanks!

"Shane Devenshire" a scris:
Hi,

Try this:

=SUMPRODUCT(--SUBSTITUTE(A1:A2,"late ",""))

assuming your two enteries are in A1:A2. On the plus side of this fomula is
that is will work for 100 cells just as well as 2. Format the cell to time.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
P

puiuluipui

Thanks!

"Sheeloo" a scris:
Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

puiuluipui said:
Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 
P

puiuluipui

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Sheeloo" a scris:
Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

puiuluipui said:
Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 
P

puiuluipui

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Rick Rothstein" a scris:
To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss

--
Rick (MVP - Excel)


puiuluipui said:
Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 
P

puiuluipui

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Shane Devenshire" a scris:
 
R

Rick Rothstein

Try this formula...

=IF(OR(A1={"","No"},B1={"","No"}),"","late
"&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss"))

--
Rick (MVP - Excel)


puiuluipui said:
Hi, i have one more problem. your code it's working but someytimes i have
a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Sheeloo" a scris:
Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

puiuluipui said:
Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif 2
Adding rota 0
if question 6
Sum 7
Using VBA to create charts 1
Formula For Time Duration Calculation 2
Status and % complete of predecessor task 0
Formatting time 2

Top