summing previously ranked values

[Guest]

hello,

i have a ranking of certain elements and the time associated with each.

set rank time
row 1 cat 1 1.5
row 2 dog 3 4
row 3 cat 3 7
row 4 cat 2 3
row 5 dog 1 1
row 6 cat 4 0 .5
row 7 dog 2 3

i want a column that returns the accumulated time already spent on that
element.

eg.
cell D4 would eaual 1.5
cell D5 would equal 0

any help really appreciated.

 

[Domenic]

SUMPRODUCT does not accept whole column references. Specify a range...

 

[bplumhoff]

Try
=SUMPRODUCT(--($A1:$A100=A1),--($B1:$B100<B1),($C1:$C100))
or another specified row-range.

Sumproduct does not work on whole columns.

HTH,
Bernd

 

[Dav]

Apart from a stray bracket i would have thought the formula should work
If you use set ranges eg a1:A????? etc it does work eg

=SUMPRODUCT(($A$1:$A$7=A2)*($B$1:$B$7<B2)*($C$1:$C$7))

Just change the 7's to the end of the range well 65535 works but 6553
does not, whether you can not use a full column i don't know

Regards

Da

 

[Guest]

Thanks everybody, the range was the problem.