Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3),
...
...
...

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range with a
Single citeria but now i have multiple Range to match with 2 citeria.

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range with
a
Single citeria but now i have multiple Range to match with 2 citeria.

Actually, we can remove one of the arrays:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A$1:F$3,A10:B10,0))),{1;1;1;1;1;1} )=2))

Biff

"T. Valko" wrote in message
...
Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

..........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range with
a
Single citeria but now i have multiple Range to match with 2 citeria.

Valko,

No numbers will repeat in a row..

I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to
say.. thanks..

Can briefly explain the formula? esp the {1,1,1,1} part...

"T. Valko" wrote:

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range with
a
Single citeria but now i have multiple Range to match with 2 citeria.

You're welcome. Thanks for the feedback!

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).

Biff

"Will" wrote in message
...
Valko,

No numbers will repeat in a row..

I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to
say.. thanks..

Can briefly explain the formula? esp the {1,1,1,1} part...

"T. Valko" wrote:

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair
of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5
(F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range
with
a
Single citeria but now i have multiple Range to match with 2 citeria.

Thanx so so much

I understn now.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).

Biff

"Will" wrote in message
...
Valko,

No numbers will repeat in a row..

I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to
say.. thanks..

Can briefly explain the formula? esp the {1,1,1,1} part...

"T. Valko" wrote:

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair
of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5
(F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range
with
a
Single citeria but now i have multiple Range to match with 2 citeria.

"Will" wrote:

Valko,

No numbers will repeat in a row..

I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to
say.. thanks..

Can briefly explain the formula? esp the {1,1,1,1} part...

"T. Valko" wrote:

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range with
a
Single citeria but now i have multiple Range to match with 2 citeria.

Chalie, i like the way you try to solve problems for others. i like u!
gorro

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).

Biff

"Will" wrote in message
...
Valko,

No numbers will repeat in a row..

I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to
say.. thanks..

Can briefly explain the formula? esp the {1,1,1,1} part...

"T. Valko" wrote:

Will any numbers be repeated on any single row:

Row 1 = 2,1,7,10,8,5
Row 2 = 5,10,11,12,20,1
Row 3 = 15,10,16,17,18,1

Is this possible:

Row 1 = 1,1,1,10,10,5

Based on you sample which doesn't have any repeats per row:

...........A..........B
10......5..........10
11......1..........10

Enter this formula in C10:

=SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2))

Copy down to C11

Biff

"Will" wrote in message
...
Dear Experts,

Which Formula shd I use so as to compute how many times a certain pair
of
numbers appeared in a few groups of 6 numbers

example

1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5
(F1)
2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1
(F2),
3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1
(F3),
..
..
..

Number (5 & 10) appear: 2 times
Number (1 & 10) appear: 3 times

I tried to use countif but it only enable me to match a Single range
with
a
Single citeria but now i have multiple Range to match with 2 citeria.