Array formula help

[Steve17701]

test.xlsx
	F	G	H	I	J	K
4	I am using Excel 2019 on Windows. I have a table of numbers I want to count the number of matches with the numbers above, In cell D3 I used a Count(Match()) array formula and it works. However, when I tab to add a new row the the formula in D3 changes. The first parameter to the Match function becomes a two dementional array, as does the the formula in D6. Why? What am I doing wrong? Is there a better way?
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1

 

[Steve17701]

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3
2
3
4	n1	n2	n3	count		I am using Excel 2019 on Windows. I have a table of numbers I want to count the number of matches with the numbers above, In cell D3 I used a Count(Match()) array formula and it works. However, when I tab to add a new row the the formula in D3 changes. The first parameter to the Match function becomes a two dementional array, as does the the formula in D6. Why? What am I doing wrong? Is there a better way?
5	1	10	3	2
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
D5	D5	=COUNT(MATCH(A5:C5,$A$1:$C$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

=SUM(COUNTIFS(Table2[@[n1]:[n3]],$A$1:$C$1))

Change Table name to suit.

 

[Steve17701]

That does not work.

 

[Fluff]

In what way?

 

[Steve17701]

The second parameter to countifs evaluates to #value. I know I can use sumproduct but the formula gets lengthy for larger data sets.

 

[Fluff]

Did you confirm the formula with Ctrl Shift Enter?

 

[Steve17701]

Oppsss, forgot that. It works now. Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Dave Patton]

You could try SumProduct.

T202303a.xlsm
	A	B	C	D	E	F
1	1	2	3
2
3
4	n1	n2	n3	count	Cumul	SumProduct
5	1	10	3	2		2
6	1	2	3	3		3
7	1	6	3	2		2
8
9
10
11	n1	n2	n3	count	Cumul	SumProduct
12	1	10	3	2		2
13	1	2	3	3		3
14	1	6	3	2		2
3b

Cell Formulas
Range	Formula
D5:D7	D5	=SUM(COUNTIFS(Table4[@[n1]:[n3]],$A$1:$C$1))
F5:F7	F5	=SUMPRODUCT(--(Table4[@[n1]:[n3]]=$A$1:$C$1))
F12:F14	F12	=SUMPRODUCT(--(A12:C12=$A$1:$C$1))