dynamic ranges with sumproduct

[Guest]

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.

 

[Roger Govier]

Hi

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

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

 

[Guest]

Well that worked thanks

Hi

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

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