Adding all the variables
 

I'm constructing a spreadsheet that uses time entries such as 11:00, 11:00A,
11:00B, etc. How do I create a formula that I can use to sum all of these
entries in a range?

Adding all the variables
 

I'm constructing a spreadsheet that uses time entries such
as 11:00, 11:00A, 11:00B, etc. How do I create a formula
that I can use to sum all of these entries in a range?

If all your entries (including the 11:00) are text, and if times before
10:00 are formatted with leading zeros, then try this...

=SUM(TIMEVALUE(LEFT(A12:A14,4)))

You would commit this formula by using Ctrl+Shift+Enter (not just Enter by
itself).

If you have some different conditions than assumed above, please tell us
what they are.

Rick

Adding all the variables
 

You get a Value error if the 11:00 is actual time

=SUM(TIMEVALUE(LEFT(A12:A14,4)))

try this array formula

=SUM(IF(OR(RIGHT(A11:A13)="a",RIGHT(A11:A13)="b"), VALUE(LEFT(A11:A13,LEN(A11:A13)-1)),A11:A13))

Commit with Ctl + Shift + Enter (CSE)

Sorry for the delay, I could not get on the computer, then I fell asleep!?

Peter



You would commit this formula by using Ctrl+Shift+Enter (not just Enter by
itself).

If you have some different conditions than assumed above, please tell us
what they are.

Rick

Adding all the variables
 

"Billy Liddel" wrote in message
...
You get a Value error if the 11:00 is actual time

I stated my assumption was that all entries were text at the beginning of my
message. I made that assumption because I figured the OP would be using a
consistent column format... since 11:00a and 11:00b are text entries and, as
such, left justified, I figured the OP would not want to mix that with
actual time values which would be right justified; hence, I assumed all
entries in the column had to be text.


=SUM(TIMEVALUE(LEFT(A12:A14,4)))

I did have an error, though... the last argument was supposed to have been
5, not 4.


Sorry for the delay, I could not get on the computer, then I fell asleep!?

Too funny! (I have done that myself.)


Rick