Number matrix

[Dude21]

I have a number matrix and want to calculate how many times one number appears 2 rows down from another in all columns. E.g. In the first column 46 appears once 2 rows down from 79 and again in column 4. Sometimes I may need to change it to 4 rows down. Thanks for you help.

79​	109​	23​	98​
72​	70​	63​	23​
46​	48​	71​	40​
120​	99​	81​	79​
51​	88​	101​	76​
75​	76​	5​	46​
39​	34​	31​	46​
110​	62​	82​	98​
51​	140​	71​	95​
41​	92​	90​	87​

 

[Snakehips]

@Dude21 Maybe, if you can clarify a couple of things it may help someone to provide a solution.
Is the matrix always 4 x 10 ?
Are you testing for a defined pair of numbers, top and bottom?

 

[Marcelo Branco]

Not sure i've understood what you're looking for. See if this does what you need.

Pasta1
	A	B	C	D	E	F	G
1	ColA	ColB	ColC	ColD		Criteria	Value
2	79	109	23	98		Num#1	79
3	72	70	63	23		Num#2	46
4	46	48	71	40		Rows	2
5	120	99	81	79
6	51	88	101	76		Result	2
7	75	76	5	46
8	39	34	31	46
9	110	62	82	98
10	51	140	71	95
11	41	92	90	8
Plan1

Cell Formulas
Range	Formula
G6	G6	=SUMPRODUCT((OFFSET(A2:D2,0,0,10-G4)=G2)*(OFFSET(A2:D2,G4,0,10-G4)=G3))

M.

 

[Dude21]

Thanks Marcelo, will try that.

Snakehips, the matrix size and numbers may change.

 

[Dude21]

That worked. Thanks again Marcelo, very elegant solution.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.