Counting combinations of numbers
 

I have some rows of numbers that I need to count certain combinations, here
is an example:
2 4 6 8
1 2 4 7
2 3 5 7
1 4 7 9

Say I want to find how many times 2 and 4 occur together (but only if they
occur in the same row) how would I accomplish this? In my example above this
would be 2. I know countif won't do it and I've tried sumproduct, but it's
quite possible I wasn't using it right. Thanks, Dave

Counting combinations of numbers
 

Will any of the numbers to be counted have duplicates in the same row?
Assuming not, with your data in the range A2:D5 -

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A2:D5,{2,4},0)),{1;1;1;1})=2))

--
Biff
Microsoft Excel MVP


"nitrousdave" wrote in message
...
I have some rows of numbers that I need to count certain combinations, here
is an example:
2 4 6 8
1 2 4 7
2 3 5 7
1 4 7 9

Say I want to find how many times 2 and 4 occur together (but only if they
occur in the same row) how would I accomplish this? In my example above
this
would be 2. I know countif won't do it and I've tried sumproduct, but it's
quite possible I wasn't using it right. Thanks, Dave

Counting combinations of numbers
 

Awesome, that seems to do the trick!! Thank you, Dave

"T. Valko" wrote:

Will any of the numbers to be counted have duplicates in the same row?
Assuming not, with your data in the range A2:D5 -

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A2:D5,{2,4},0)),{1;1;1;1})=2))

--
Biff
Microsoft Excel MVP


"nitrousdave" wrote in message
...
I have some rows of numbers that I need to count certain combinations, here
is an example:
2 4 6 8
1 2 4 7
2 3 5 7
1 4 7 9

Say I want to find how many times 2 and 4 occur together (but only if they
occur in the same row) how would I accomplish this? In my example above
this
would be 2. I know countif won't do it and I've tried sumproduct, but it's
quite possible I wasn't using it right. Thanks, Dave

Counting combinations of numbers
 

I just want to make sure that I modified the formula correctly for the actual
use. The data is in rows of six numbers.
=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH('Original
#''s'!B1:G1205,{1,2},0)),{1;1;1;1;1;1})=2))
Also, if you don't mind, could you explain how the formula works?
Thank you, Dave

"nitrousdave" wrote:

Awesome, that seems to do the trick!! Thank you, Dave

"T. Valko" wrote:

Will any of the numbers to be counted have duplicates in the same row?
Assuming not, with your data in the range A2:D5 -

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A2:D5,{2,4},0)),{1;1;1;1})=2))

--
Biff
Microsoft Excel MVP


"nitrousdave" wrote in message
...
I have some rows of numbers that I need to count certain combinations, here
is an example:
2 4 6 8
1 2 4 7
2 3 5 7
1 4 7 9

Say I want to find how many times 2 and 4 occur together (but only if they
occur in the same row) how would I accomplish this? In my example above
this
would be 2. I know countif won't do it and I've tried sumproduct, but it's
quite possible I wasn't using it right. Thanks, Dave

Counting combinations of numbers
 

Yes, that formula is correct. You need an array of 1s equal to the number of
columns in the matrix.

Here's an explanation I posted a while back on the exact same subject:

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
Microsoft Excel MVP


"nitrousdave" wrote in message
...
I just want to make sure that I modified the formula correctly for the
actual
use. The data is in rows of six numbers.
=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH('Original
#''s'!B1:G1205,{1,2},0)),{1;1;1;1;1;1})=2))
Also, if you don't mind, could you explain how the formula works?
Thank you, Dave

"nitrousdave" wrote:

Awesome, that seems to do the trick!! Thank you, Dave

"T. Valko" wrote:

Will any of the numbers to be counted have duplicates in the same row?
Assuming not, with your data in the range A2:D5 -

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A2:D5,{2,4},0)),{1;1;1;1})=2))

--
Biff
Microsoft Excel MVP


"nitrousdave" wrote in message
...
I have some rows of numbers that I need to count certain combinations,
here
is an example:
2 4 6 8
1 2 4 7
2 3 5 7
1 4 7 9

Say I want to find how many times 2 and 4 occur together (but only if
they
occur in the same row) how would I accomplish this? In my example
above
this
would be 2. I know countif won't do it and I've tried sumproduct, but
it's
quite possible I wasn't using it right. Thanks, Dave