On 2/5/2010 5:21 PM, Bob Long wrote:
Andreas Saeger wrote:
Andy Chaplin wrote:
If there a simple way to count the number of different values in a
range?
If I have a column with a thousand rows and I have different values in
the rows (some of which are repeated)...
Bacon
Eggs
Toast
Marmalade
Toast
Toast
Bacon
The answer I want here is 4. (the total of different entries)
I guess there must be a function for this, but I can's seem to find it.
Any help would be appreciated.
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_FREQUENCY_function
Or a data pilot with the same column as row field and data field with
function "Count".
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/DataPilot
I'm not the original poster, but I tried the data pilot method for
interest, but I'm not sure I have worked it out properly. I pasted the
sample above into A2:A8, adding a heading "Items" in A1 (and ensuring
no trailing spaces in any item cell). Then:
Click on A1
Data
Data Pilot
Start
Current Selection
OK
Drag the "Items" button into "Row Fields"
Drag the "Items" button into "Data Fields"
Double click on "Items - Sum" and change the function to "Count
OK
OK.
That's good; I get a table with individual counts for each item and a
Total of 7. There are 4 rows of the now grouped items. But is there a
way that the Data Pilot process can tell me that "4" as an answer?
I know I can add another formula =COUNT(B13:B16) to get that answer
(in this case the data pilot results are in A12:B17) but if the items
change the data pilot results change, changing the number of rows in
the result, making the COUNT formula incorrect.
Am I missing something?
Bob:
From what I have found using Excel 03, it is not built-in. Calc's Data
Pilot does not seem to feature it either. One of the Excel workarounds
was to add a new column to the Pivot data table with the following
formula in the data column:
IF(COUNTIF(A2:A2,A8)=1,1,0)
Calc syntax is:
IF(COUNTIF(A2:A8;A8)=1;1;0)
Now you can manipulate the Data Pilot to show only the unique Items
(hiding the "0"'s)
TomW
