dynamic ranges with sumproduct
 

trying to use sumproduct fuction with dynamic ranges,
Example "column a" has problem as heading and "column j" has status as the
heading,
I put in a formula of somthing like
=sumproduct((problem="test")*(status="ok")) which should be the same as
putting in
=sumproduct((a1:a200="test")*(j1:j200="ok")) but all I'm getting is a #NUM!
error, is it possiable to use the dynamic range name with sumproduct.

dynamic ranges with sumproduct
 

Hi

Have you defined the dynamic ranges?
Just typing the column heading does not act as a dynamic range.

Try
InsertNameDefineName problem Refers to
=OFFSET($A$2,0,0,COUNTA($A:$A)-1)
Repeat using status as Name and column J instead on column A
--
Regards

Roger Govier


"Dreamstar_1961" wrote in
message ...
trying to use sumproduct fuction with dynamic ranges,
Example "column a" has problem as heading and "column j" has status as
the
heading,
I put in a formula of somthing like
=sumproduct((problem="test")*(status="ok")) which should be the same
as
putting in
=sumproduct((a1:a200="test")*(j1:j200="ok")) but all I'm getting is a
#NUM!
error, is it possiable to use the dynamic range name with sumproduct.

dynamic ranges with sumproduct
 

Well that worked thanks

"Roger Govier" wrote:

Hi

Have you defined the dynamic ranges?
Just typing the column heading does not act as a dynamic range.

Try
InsertNameDefineName problem Refers to
=OFFSET($A$2,0,0,COUNTA($A:$A)-1)
Repeat using status as Name and column J instead on column A
--
Regards

Roger Govier


"Dreamstar_1961" wrote in
message ...
trying to use sumproduct fuction with dynamic ranges,
Example "column a" has problem as heading and "column j" has status as
the
heading,
I put in a formula of somthing like
=sumproduct((problem="test")*(status="ok")) which should be the same
as
putting in
=sumproduct((a1:a200="test")*(j1:j200="ok")) but all I'm getting is a
#NUM!
error, is it possiable to use the dynamic range name with sumproduct.