Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Array
 

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

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Array
 

Bob

If you want to use countif as if it were array entered into a cell, bu
dont have it in a cell then use application.evaluate("countif formula a
a string") as in

Application.Evaluate("=SUM(1/COUNTIF(A1:A6, A1:A6))")


Cheers
Simo

--
Simon Murph
-----------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...fo&userid=2653
View this thread: http://www.excelforum.com/showthread.php?threadid=47123

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Ar
 

Simon,

This may turn the trick, but it may not.

It's not just a matter of evaluating a formula, but to evaluate an array
formula.

I believe there are two properties, Formula and FormulaArray, that are
associated with a cell. When you hit '(Enter)', the expression gets assigned
to the Formula property. When Ctrl-Shift-Enter, to the latter.

I'll experiment with this and let the list know.


--
Bob Sullentrup


"Simon Murphy" wrote:


Bob

If you want to use countif as if it were array entered into a cell, but
dont have it in a cell then use application.evaluate("countif formula as
a string") as in

Application.Evaluate("=SUM(1/COUNTIF(A1:A6, A1:A6))")


Cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=471231

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Array
 

Bob
App.evaluate uses array evaluation rules thats why I suggested it.

Are you saying you want to enter this in a cell? If so why are yo
bothering with VSTO?
If you are writing a user defined function you can code it how you wan
so you don't need array evaluation, to do that you need to create a
automation add-in not a VSTO project.

Not totally clear what you are after, if this doesn't help you pos
back more info on what you are doing.
Cheers
Simo

--
Simon Murph
-----------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...fo&userid=2653
View this thread: http://www.excelforum.com/showthread.php?threadid=47123

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Ar
 

Simon,

<App.evaluate uses array evaluation rules

I did not know that.

That's sufficient, then, for my purposes. Many thanks!


--
Bob Sullentrup


"Simon Murphy" wrote:


Bob
App.evaluate uses array evaluation rules thats why I suggested it.

Are you saying you want to enter this in a cell? If so why are you
bothering with VSTO?
If you are writing a user defined function you can code it how you want
so you don't need array evaluation, to do that you need to create an
automation add-in not a VSTO project.

Not totally clear what you are after, if this doesn't help you post
back more info on what you are doing.
Cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=471231

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Ar
 

Simon,

Yup, your suggestion works slicker than snot on a doorknob!


--
Bob Sullentrup


"Bob Sullentrup" wrote:

Simon,

<App.evaluate uses array evaluation rules

I did not know that.

That's sufficient, then, for my purposes. Many thanks!


--
Bob Sullentrup


"Simon Murphy" wrote:


Bob
App.evaluate uses array evaluation rules thats why I suggested it.

Are you saying you want to enter this in a cell? If so why are you
bothering with VSTO?
If you are writing a user defined function you can code it how you want
so you don't need array evaluation, to do that you need to create an
automation add-in not a VSTO project.

Not totally clear what you are after, if this doesn't help you post
back more info on what you are doing.
Cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=471231

Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Array
 

Bob
Post back if it doesn't do what you need
cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=471231