Folks,

I want to have a function return the number of distinct entries in a column
in Excel using VSTO.

Turns out, 'CountIf' is a marvelously clever function for doing this with
one caveat -- that you have to signal to the 'Sum' function to mark it as an
array function.

Suppose your list is 'Apples', 'Apples', 'Pears', 'Grapes', 'Grapes',
'Grapes' in the range A1:A6.

=CountIf(A1:A6, "Grapes") returns 3 since 'Grapes' occurs three times.

Consider this though:

=Sum(1/CountIf(A1:A6, A1:A6))

CountIf does the same thing but operates on each cell in the list A1:A6
since the second parameter is a range, not the single value 'Grapes'. So it
returns an array {2,2,1,3,3,3} indicating the list has two 'Apples', then one
'Pear' then three 'Grapes'.

Taking reciprocals, we get {1/2, 1/2, 1, 1/3, 1/3, 1/3}.

When we sum the array, we get 3, the number of distinct entries in the list.

THE TRICK IS TO ENTER THE FUNCTION INTO A CELL

=Sum(1/CountIf(A1:A5, A1:A5))

not with 'Enter', but with Ctrl-Shift-Enter to mark it as an array function.

In VSTO, we can readily execute certain Excel functions using
'ThisApplication.WorksheetFunction'. It looks to me as if
'ThisApplication.WorksheetFunction.CountIf' does not know how to process
its second parameter as a range and thus an array. Equivalently, there is
no counterpart of the Ctrl-Shift-Enter functionality in the code behind that
exists in native Excel.

Any clues?

Thanks, Bob Sullentrup
--
Bob Sullentrup