If a cell has a similar value to the one above it, total adjacent columns

Hi One and All,

I would normally ask for help from a colleague but as its a saturday he isn't around and I would like to get this to work today if at all possible.

Here's what I am after trying to achieve:

If a cell has a date in it that is the same as a cell above, I would like to total time values in cells that are adjacent to these original cells. Maybe the example below explains it better.

A1 A2 A3
01/01/09 1:00
01/01/09 3:00 4:00
02/01/09 0:10 0:10
03/01/09 0:50
03/01/09 0:50 1:40

By A1:A3 I presume you mean A1:C1 ?

If so, formula in C1 (first possible result)

=IF($A2=$A1,"",SUMIF($A$1:$A1,$A1,$B$1:$B1))
copied down for all rows
(format cells as Time - perhaps to [hh]:mm - not clear as to your preference)

Implication then being that the values are times stored as text...

either

a) highlight column B -> run Data -> Text to Columns -> click Finish

b) use a more expensive formula in column C

C1: =IF($A2=$A1,"",SUMPRODUCT(($A$1:$A1=$A1)*$B$1:$B1))
copied down

If the above don't work then encase $B$1:$B1 within TRIM and try again... if that doesn't work we'll want to see an example ... (probably a CHAR(160) error or somesuch)

...is there anything I can do other than change the source data?

Have you tried the SUMPRODUCT as suggested ?

I have tried to change the format to [h]:mm but this does not work

Changing the format of a cell does not alter the underlying data type of the value already within it... eg A1: Apple... formatting A1 as Number does not in turn convert Apple into a number - the same is true of any string including "number strings"