summing previously ranked values

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.

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

Sumproduct does not work on whole columns.

HTH,
Bernd

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

In article <[email protected]>,
ellebelle <[email protected]> wrote:

> I have tried using this formula
>
> =SUMPRODUCT(--($A:$A=A1,--($B:$B<B1)),($C:$C))
>
>
> where: A = set; B = rank; and C = time
>
> but ii get the NUM! error message.
>
>
>
>
>
> "ellebelle" wrote:
>
> > 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.

Thanks everybody, the range was the problem.

"Dav" wrote:

>
> 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 65536
> does not, whether you can not use a full column i don't know
>
> Regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=508043
>
>