Sunday, April 8, 2012

yogi_Compute Unique Entities In A Range Of Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #498    Apr 8, 2012

user DeeEffGee said: 
Array Function Calculation Limit?
I am using an ArrayFunction wrapper to count the number of unique rows in an array. Formula: =ArrayFunction(SUM(1/COUNTIF(B210:E1273,B210:E1273))) I will want to increase the size of this data range in the future, as well, as I update this file several times a week. 
It seems Google Spreadsheets can't handle this size of an array, because whenever I try to do the calculation, it sits there for a while with the "Working..." badge and then says "Trying to reach" and never finishes. I see that there are stated limits for a spreadsheet's size, but not for what a formula can operate on. 
Is there anything I can do to make this work, or am I just bumping up against Google Docs limitations? 
following is a solution to the problem
I have not checked whether the formula using the COUNTIF function does choke when used over a large range ... however, alternately I have also used the COUNTUNIQUE function