ansaurus

Question

Counting distinct values in excel - frequency function

Answer 1

+1 A:

Enter your numbers in column A and a sequence in colun B

Select both columns and create a pivot table putting col A in rows. Select {COUNT} as function and you are done.

belisarius 2010-07-22 18:52:26

so in B1 i WANT the totals for value #1, and in B2 I want the toltals for value number 2 and in B3 the value total for number 3

Mike 2010-07-22 19:04:46

I have on coumn with a total of 3000 rows with values ranging from 13. I am looking for a two column result totaling each number 1 to 13

Mike 2010-07-22 19:08:41

thanks for your help ... I will try something else.My fault, I maybe not explaining it well :( I am taking one column with numerous number and want the distinct values total for each number in that column with the results in rows ranging from 1 - 14..

Mike 2010-07-22 19:43:35

@ belisarius: you can even do this without column B; drag the field to Row Labels and select Count. Only problem with the pivot is it won't display a 0 for the values that are not listed. So 2 and 6, which should be 0, are missing.

Mathias 2010-07-23 05:14:01

@Mathias You are right. I've tried to guide Mike through this (I deleted a lot of earlier comments ... ended up with this as I thought is an easy way to understand the process. Previously I posted an easy matrix formula with {Frequency} that satisfied the "show the zeroes condition", but the OP rejected it, I didn't understand why ... (you can look at it in the *edits*). I find more difficult to post a solution to an Excel question than to tougher problems. My fault, surely.

belisarius 2010-07-23 09:12:01

Answer 2

A:

that was an error ...

simply I want to add up the unique number in the "search" column

and results

columns and rows unique number with the total of that value

A1......B1
1........2
2........0
3........2 ----correction :(
4........1
5........1

EXAMPLES OF WHAT i HAVE TRIED =SUM(IF(FREQUENCY(Stats.CD8:CD2776,DrawStats.CD8:CD2776)>0,1)) Err:508

=FREQUENCY(Stats.CD6:CD2774;A8) =FREQUENCY(Stats.CD8:CD2776;A10)

Mike 2010-07-22 18:54:35

Edit your question, do not post an answer ...

belisarius 2010-07-22 19:00:32

Answer 3

+1 A:

You can use COUNTIF to count the number of elements that match a condition.
Suppose you have your numbers in column A, say from A1 to A10:

A1: 1
A2: 3
A3: 7

etc...
Type in somewhere on your sheet, say in column B, the values you are interested in:

B1: 0
B2: 1

etc...
and in C1, type in
=COUNTIF($A$1:$A$10, B1)

This should count the number of values equal to B1 (i.e. 0), in A1:A10.

Mathias 2010-07-23 05:26:04

+1, yep .......

belisarius 2010-07-23 09:14:26

ansaurus

tags:

views:

answers:

Counting distinct values in excel - frequency function

related questions