COUNTIF
 

How are your numbers arranged? Are they comma separated strings in one cell
as in your example, or are they stored in separate cells, e.g. in range A1:A6
like that:
A1 1
A2 2
etc.
Also, how are arranged the pairs of numbers?

Regards,
Stefi


€žWill€� ezt Ã*rta:

Hi,

I have a project to count how freq a pair of numbers appear amount a few
groups of 6 numbers.

Lets say I have 2 pairs of 6 numbers, (1,2,3,4,5,6) & (1,3,5,7,9) and pairs
(1,3) & (3,5) appears twice in these 2 pairs of 6 numbers. I am unable to
find a suitable formula for this problem and the nearest I can do is COUNTIF
but COUNTIF can only cater 1 CRITERIA but in these i believe I have 2 or
more.

COUNTIF
 

You do not have to enter the formula 20-30 times, you have to fill down the
formula through 20-30 rows!
Regards,
Stefi


€žWill€� ezt Ã*rta:

Stefi,

problem solved.. if i have 20-30 groups of 6, meaning i have to do tis
formula 20-30 times?

"Stefi" wrote:

This is probably not the optimal sulution but for the moment I couldn't find
the single formula (I guess it should be an array formula) fulfilling your
request. Say your data are arranged in this pattern:
groups of 6 pairs result
A1 ... F1 H1 I1 J1
A2 ... F2 J2
J3

Enter in J1
=IF(AND(COUNTIF($A1:$F1,H$1)=1,COUNTIF($A1:$F1,I$1 )=1),1,0)
Fill it down to the last group of 6 (2 in my example)
Autosum column J in J3

Regards,
Stefi


€žWill€� ezt Ã*rta:

Stefi,

my numbers are arranged in different cells in a row.. A1(1) B1(2) C1(3) etc..

My pairs of numbers are arranged also in different cells.

"Stefi" wrote:

How are your numbers arranged? Are they comma separated strings in one cell
as in your example, or are they stored in separate cells, e.g. in range A1:A6
like that:
A1 1
A2 2
etc.
Also, how are arranged the pairs of numbers?

Regards,
Stefi


€žWill€� ezt Ã*rta:

Hi,

I have a project to count how freq a pair of numbers appear amount a few
groups of 6 numbers.

Lets say I have 2 pairs of 6 numbers, (1,2,3,4,5,6) & (1,3,5,7,9) and pairs
(1,3) & (3,5) appears twice in these 2 pairs of 6 numbers. I am unable to
find a suitable formula for this problem and the nearest I can do is COUNTIF
but COUNTIF can only cater 1 CRITERIA but in these i believe I have 2 or
more.

COUNTIF
 

stefi,

thanks, wat i mean is I have 20-30 groups of 6 and I need to find out how
often a certain pair appear..

Meaning 1st - 30th group of 6 numbers, pair 1 & 5 appear 20 times...
somethin like tat

"Stefi" wrote:

You do not have to enter the formula 20-30 times, you have to fill down the
formula through 20-30 rows!
Regards,
Stefi


€žWill€� ezt Ã*rta:

Stefi,

problem solved.. if i have 20-30 groups of 6, meaning i have to do tis
formula 20-30 times?

"Stefi" wrote:

This is probably not the optimal sulution but for the moment I couldn't find
the single formula (I guess it should be an array formula) fulfilling your
request. Say your data are arranged in this pattern:
groups of 6 pairs result
A1 ... F1 H1 I1 J1
A2 ... F2 J2
J3

Enter in J1
=IF(AND(COUNTIF($A1:$F1,H$1)=1,COUNTIF($A1:$F1,I$1 )=1),1,0)
Fill it down to the last group of 6 (2 in my example)
Autosum column J in J3

Regards,
Stefi


€žWill€� ezt Ã*rta:

Stefi,

my numbers are arranged in different cells in a row.. A1(1) B1(2) C1(3) etc..

My pairs of numbers are arranged also in different cells.

"Stefi" wrote:

How are your numbers arranged? Are they comma separated strings in one cell
as in your example, or are they stored in separate cells, e.g. in range A1:A6
like that:
A1 1
A2 2
etc.
Also, how are arranged the pairs of numbers?

Regards,
Stefi


€žWill€� ezt Ã*rta:

Hi,

I have a project to count how freq a pair of numbers appear amount a few
groups of 6 numbers.

Lets say I have 2 pairs of 6 numbers, (1,2,3,4,5,6) & (1,3,5,7,9) and pairs
(1,3) & (3,5) appears twice in these 2 pairs of 6 numbers. I am unable to
find a suitable formula for this problem and the nearest I can do is COUNTIF
but COUNTIF can only cater 1 CRITERIA but in these i believe I have 2 or
more.