1

Possible Duplicate:
Excel: how to count number of distinct values in a range?

I would like to create a custom function COUNTIFUNIQUES that extends the functionality of COUNTIFS.

Here is an example of how it would work:

     A1  B1
A1   A   1
A2   A   1
A3   A   2
A4   B   Y
A5   B   Z
A6   B   Z
A7   C   4
A8   C   5
A9   C   6


Here is the existing functionality:
=COUNTIFS(criteria_range[1], criteria[1], ...)

=COUNTIFS(A1:B9,"A") —> 3
=COUNTIFS(A1:B9,"B") —> 3

I think the desired functionality requires two improvements:

First: change COUNTIFS from *COUNTIFS(criteria_range[1], criteria[1], ...)* to
*COUNTIFS(count_range, criteria_range[1], criteria[1], ...)*


Second: have the function return unique result:

=COUNTIFUNIQUES(B1:B9,A1:A9,"A") —> 2
=COUNTIFUNIQUES(B1:B9,A1:A9,"B") —> 2

How would you implement this in VBA?

What I'm looking for is something in the form:

Function COUNTIFUNIQUES(...)

....

End Function

I don't believe any existing solution solves this problem "cleanly", if at all.

Community
  • 1
vg425
  • 103
  • 1
  • 1
  • 5
  • Maybe some typos in your example? The range of values is A1:B9 to get 3 (otherwise it would be 0). Also, from the name, it sounds like you want to count the "unique" incidence of something, but why give a single parameter like "A"? Wouldn't the unique incidence be by definition either 1 or none/false? Otherwise, you've got the right idea and just need to show some effort on coding your solution or more research on the available VBA code examples. Without it, the question could be closed as a "homework" request. – jdh Feb 05 '13 at 15:47
  • Not really the same question at all for two reasons. First, the question you reference asks for a solution for counting distinct values in a single column (COUNTDISTINCT), not a conditional COUNTDISTINCTIF. Second, I'm specifically asking how to create a custom function which would make it much simpler to use this feature repeatedly. – vg425 Feb 05 '13 at 17:28

0 Answers0