SUMPRODUCT - use last cell function for range

[SteveT]

Hello,

I would like to use sumproduct function to determine how many criterias met
in 2 columns. The range of value will change daily so I'd like to use the
address(counta()) function as the close of ea range within sumproduct
function.

ie.
sumproduct((Sheet1$A$1:address(counta(A:A),1)=a1)*(Sheet1$J$1:address(count(a:a),10)="no"))

I accomplished this once many moons ago but apparently I've forgotten a step
and cannot find the original formula that accomplished. Can anyone out there
help?

Thanks, Steven

 

[Bob Phillips]

=SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(A:A))=A1)*(Sheet1!$J$1:INDEX(Sheet1!$J:$J,COUNTA(A:A))="no"))

 

[SteveT]

Thank You Bob, that did it ! BR, Steven

=SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(A:A))=A1)*(Sheet1!$J$1:INDEX(Sheet1!$J:$J,COUNTA(A:A))="no"))

--
__________________________________
HTH

Bob