Count matching values, except...

[BWMagee]

The formula I wish to write has three rules:
1) count values in $E21:$AJ21
2) that match a value in $E$21:$A$J164
3) except when text in $C$21:$C$164 = $C21

Eg [using range E21:H24 instead of E21:AJ164].

	C	E	F	G	H
21	One	1	2	3	4
22	Two	1.5	2.5	3.5	4.5
23	Three	1.8	2	4	4.1
24	One	1	2	3	4

The correct answer here would be 2. From row 21, we can see the value 2 also appears in row 23 (so thats a count of 1), as does the value 4 (so thats a count of 1). They are the only values to appear more than once, so 1+1=2. We can see that every value in row 21 is found again in row 24, but as the text in C21 is the same as C24, we are ignoring that row.

I currently have the below formula, which calculates rules 1 and 2 perfectly, but I don't know how to include rule 3 within it. Any ideas?

=COUNT($E21:$AJ21)-SUMPRODUCT(--(COUNTIF($E$21:$AJ$164,$E21:$AJ21)=1))

All help is much appreciated!

 

[CyrusTheVirus]

Couple questions:

1) Will you be copying this formula down or over?
2) Why are you referencing starting in row 21, will you always?

 

[BWMagee]

I will be copying this formula down. Not over.
I'm starting in row 21 as that is where the table starts. The table is from 21 to 164. There will be other tables using the same formula, and they will have a different starting point, but I don't mind if I have to change the starting point individually for each table.

Given the number of tables using this, it will feature in thousands of rows. I'm a little concerned SUMPRODUCT may slow the sheet down too much over that span, but won't know until I've tried it. If so, the formula may need to be changed to SUMIF instead (if it can).

 

[BWMagee]

Also, the formula is being placed in cell A21.

 

[CyrusTheVirus]

So in the above example when copying down to cell A22, do you still need to reference row 21 or only row 22 and below?

 

[CyrusTheVirus]

Will the below give you what you are looking for? If it were me, I'd go with the helper columns as mentioned below. Let me know if this works for you.

A21 copied down:

=SUM(H22:K22)

H21 copied down and over:

=SUMPRODUCT((D22=$D$22:$G$25)*($C$22:$C$25<>$C22))

A	B	C	D	E	F	G	H	I	J	K
		Number	Amount 1	Amount 2	Amount 3	Amount 4	Helper 1	Helper 2	Helper 3	Helper 4
2​		One	1.00	2.00	3.00	4.00	0​	1​	0​	1​
0​		Two	1.50	2.50	3.50	4.50	0​	0​	0​	0​
4​		Three	1.80	2.00	4.00	4.10	0​	2​	2​	0​
2​		One	1.00	2.00	3.00	4.00	0​	1​	0​	1​

 

[BWMagee]

Thanks very much Cyrus, that works! I hadn't thought of helper columns.
I can also rewrite the formula if I need to avoid the SUMPRODUCT formula:
=COUNTIF($D$2:$G$5,D2)-COUNTIFS($C$2:$C$5,$C2,D$2:D$5,D2)