HELP! - Formula suggestion

W

Will

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.
 
T

T. Valko

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
 
T

T. Valko

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
 
W

Will

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

T. Valko

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
 
W

Will

Thanx so so much

I understn now.

T. Valko said:
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
 
W

Will

Will said:
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top