|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
Paul
Two methods
You could filter for unique values and then use subtotal =subtotal (2,range)
You could use a pivot table (which more than uses an extra column 
After that, all the methods I have use another column.
Paul,
You can find the explanation – and a lot more – in the link (in my previous reply) to the paper on using array formulas by Bob Umlas.
Here is a “simple” (ahem) example: say that A1 to A6 contain the numbers 6, 7, 8, 7, 6, 6
In another cell, say B1, enter the array formula =SUM(1/COUNTIF(A1:A6,A1:A6)). Its value will be 3.
Now, with B1 selected, click in the formula bar.
Select COUNTIF(A1:A6,A1:A6) and evaluate it by pressing F9. (If you make a mistake, cancel the edit by pressing Esc and start anew).
Ordinarily, COUNTIF is used with a range as first argument, and a single condition as second argument. It tests every cell in the first argument for the condition, and counts the number of cells that satisfy the condition. Here, the second argument is a range too, so the formula returns a range – a count for each element of the second argument. For instance, the first value returned will be 3, since 6 occurs three times.
So, COUNTIF(A1:A6,A1:A6) will be replaced by {3,2,1,2,3,3} indicating that 6 occurs 3 times, 7 occurs 2 times, 8 occurs 1 time, 7 occurs 2 times, 6 occurs 3 times and 6 occurs 3 times.
Now, select 1/{3,2,1,2,3,3} and evaluate it by pressing F9.
You’ll get {1/3,1/2,1,1/2,1/3,1/3} expressed as decimal numbers. As you see, each number that occurs once counts as 1 here, each number that occurs twice counts as 1/2 and each number that occurs 3 times counts as 1/3.
So if we add this, each unique number will count as 1 (1/2+1/2=1 and 1/3+1/3+1/3=1).
Are you still with me? I always get 
thinking about array formulas.
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Notifications
