Counting

Lewy2 · Mar 18th, 2002, 02:41 PM

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 · Mar 18th, 2002, 02:54 PM

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 · Mar 18th, 2002, 03:03 PM

Quote:

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 · Mar 18th, 2002, 03:59 PM

Quote:

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

Quote:

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 · Mar 18th, 2002, 04:17 PM

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 · Mar 18th, 2002, 11:33 PM

> I cant Get either of the relies to work

Lewy,

What happens when you apply the FREQUENCY formula?

Aladin

Mar 18th, 2002, 02:54 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 02:41 PM	#1
Lewy2 New Member Join Date: Mar 2002 Location: Surfers Paradise Posts: 19	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

Mar 18th, 2002, 04:17 PM	#5
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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.

Mar 18th, 2002, 11:33 PM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	> I cant Get either of the relies to work Lewy, What happens when you apply the FREQUENCY formula? Aladin