Counting

[Lewy2]

I have a column that has text entered into it
like the following.
1-1
1-1
1-2
1-3
1-4
1-4
I want to be able to count the number of sets that occur more than once.
ie the above would have aresult of 2 being 1-1 & 1-4

 

[Al Chara]

Try using the countif function:

=IF(COUNTIF($A$1:$A$6,A1)>1,COUNTIF($A$1:$A$6,A1),"")

Where $A$1:$A$6 is your data range and A1 is the value you are searching for. The function will only display values that occur more than once.

 

[Aladin Akyurek]

On 2002-03-18 13:41, Lewy2 wrote:
I have a column that has text entered into it
like the following.
1-1
1-1
1-2
1-3
1-4
1-4
I want to be able to count the number of sets that occur more than once.
ie the above would have aresult of 2 being 1-1 & 1-4

=SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>1,1))

where A2:a7 houses the sample you provided.

 

[Lewy2]

On 2002-03-18 14:03, Aladin Akyurek wrote:

On 2002-03-18 13:41, Lewy2 wrote:
I have a column that has text entered into it
like the following.
1-1
1-1
1-2
1-3
1-4
1-4
I want to be able to count the number of sets that occur more than once.
ie the above would have aresult of 2 being 1-1 & 1-4

=SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),MATCH(A2:A7,A2:A7,0))>1,1))

where A2:a7 houses the sample you provided.

I cant Get either of the relies to work thanks for the effort
Regards
Lewy

 

[Steve Hartman]

They need to be entered by pressing Control-Save-Enter at the same time instead of just enter since they are array, oops I mean CSE, formulas.

 

[Aladin Akyurek]

> I cant Get either of the relies to work

Lewy,

What happens when you apply the FREQUENCY formula?

Aladin