I'm setting a formula using this line of code
worksheet.Cells(row, 2).Formula = "=SUMPRODUCT((C2:C" + CStr(row) + "<>"""")/COUNTIF(C2:C" + CStr(row) + ",C2:C" + CStr(row) + "&""""))"
When I open the worksbook in excel it show #VALUE. If I select the cell, click in the edit area and simple press enter it correctly calculates the results. What do I need to do the have it correctly calculate by simply opening the document in excel.
By the way, I got the formula from here, http://www.mrexcel.com/archive2/63500/73502.htm. It returns a count of distinct values. It would also be nice if there was a simpler formula to do this.