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
|