Count instances of combinations

[asad]

Hello Guys,

I tried but can't get around to work out how to get the combinations of a set of numbers to match up with a range. The data looks like below:

Excel Workbook
	D	E	F	G	H	I	J	K	L
1	37	39	1	14	18	31	30		1
2	23	25	27	5	1	40	13		1
3	5	31	14	40	30	10	12		1
4	1	20	17	28	2	13	19		1
5	40	11	28	15	27	29	9		1
6	32	9	15	24	40	12	14		1
7	12	26	7	37	38	17	13		1
Sheet2

But, I want the formula to check if the numbers in D1:J1 are in a same row then it should be included. They could be in any sequence. D1 could be in E20 but then rest of the numbers from E1:J1 have to be in row 20.

Is it possible or am I just banging head against a brick wall?

Asad

 

[asad]

I tired the following

=SUMPRODUCT(--(D$1:J$500=D1),--(D$1:J$500=E1),--(D$1:J$500=F1),--(D$1:J$500=G1),--(D$1:J$500=H1),--(D$1:J$500=I1),--(D$1:J$500=J1))

But no success.

 

[MikeWx]

If the values in Line 20 did match D1:J1, what are you supposed to see in Column L? Are you looking for the row that has the matching values for D1:J1 or something else?

 

[asad]

Thanks for the response.
I just want to know how many times that combination appears in the whole range which extends to 500 rows, and give me the count number for that. So if all numbers in row 1 appear in 3 other rows say row 120, row 290, and row 300, then it should give me 3 as answer.

Asad

 

[T. Valko]

Thanks for the response.
I just want to know how many times that combination appears in the whole range which extends to 500 rows, and give me the count number for that. So if all numbers in row 1 appear in 3 other rows say row 120, row 290, and row 300, then it should give me 3 as answer.

Asad

As long as there are no duplicate numbers in any row...

Book1
	D	E	F	G	H	I	J
1	37	39	1	14	18	31	30
2	23	25	27	5	1	40	13
3	5	31	14	40	30	10	12
4	1	14	18	30	31	37	39
5	40	11	28	15	27	29	9
6	32	9	15	24	40	12	14
7	39	37	31	30	18	14	1
Sheet1

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(D2:J7,D1:J1,0))),{1;1;1;1;1;1;1})=7))

 

[Marcelo Branco]

With duplicates a possible solution (not elegant) is to use a helper-column, say column X, with this formula in X2 copied down till X500

=COUNTIF(D2:J2,$D$1)>0+COUNTIF(D2:J2,$E$1)>0+.......+COUNTIF(D2:J2,$J$1)>0

and
=COUNTIF($X$2:$X$500,7)

M.

 

[Marcelo Branco]

Sorry, i missed the parenthesis...

X2
=(COUNTIF(D2:J2,$D$1)>0)+(COUNTIF(D2:J2,$E$1)>0)+.......+(COUNTIF(D2:J2,$J$1)>0)

M.

 

[asad]

Thanks Biff and Marcelo.

Biff's formula does what I wanted and in the manner I wanted. Thanks a lot again.

Asad

 

[T. Valko]

Thanks Biff and Marcelo.

Biff's formula does what I wanted and in the manner I wanted. Thanks a lot again.

Asad

You're welcome. Thanks for the feedback!

 

[Marcelo Branco]

Thanks Biff and Marcelo.

Biff's formula does what I wanted and in the manner I wanted. Thanks a lot again.

Asad

Tks for the feedback

M.