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.

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

In article ,
ellebelle wrote:

I have tried using this formula

=SUMPRODUCT(--($A:$A=A1,--($B:$B<B1)),($C:$C))


whe 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.

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

Sumproduct does not work on whole columns.

HTH,
Bernd

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

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