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

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

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

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

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