Adding all the variables

W

wwtrader

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?
 
R

Rick Rothstein \(MVP - VB\)

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?
Click to expand...

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
 
B

Billy Liddel

You get a Value error if the 11:00 is actual time
=SUM(TIMEVALUE(LEFT(A12:A14,4)))
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

Billy Liddel said:
You get a Value error if the 11:00 is actual time
Click to expand...

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.


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!?
Click to expand...

Too funny! (I have done that myself.)


Rick
 
Top