SUMPRODUCT - use last cell function for range
 

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

SUMPRODUCT - use last cell function for range
 

=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

"SteveT" wrote in message
...
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

SUMPRODUCT - use last cell function for range
 

Thank You Bob, that did it ! BR, Steven



"Bob Phillips" wrote:

=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

"SteveT" wrote in message
...
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