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?

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

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

"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