Counting cells for a condition within a moving subrange of a total range

[Lights_out]

I have imported some data using VBA in an excel file. I have a 100's of parts numbers in column A and corresponding 6 numerical values for each part in column C. (For example, if "A5" has part number then data would be in "C5:C10"). I also have a numerical criteria set in another cell "B2".
I want to count the number of parts that failed and put that number in "D2". The failure criteria is if any 1 out of 6 values for a part is above the set limit in "B2", then it is considered as failed. I want to apply it to all the parts in the sheet.
I was able to make it work for 1 part using OR function, but I cannot figure out how to apply this for the other group of cells in the whole column C where all the data is. I could use VBA but I don't know if changing "B2" would update "D2" automatically. See attached image for more clarity.
Thank you.

https://imgur.com/a/zfA5a5g

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​
1​
2​	Peak % limits	50​	Parts failed	2​
3​
4​
5​	1808A66346347		99.006​
6​	1808A66346347		73.492​
7​	1808A66346347		32.328​
8​	1808A66346347		9.915​
9​	1808A66346347		52.913​
10​	1808A66346347		8.115​
11​	1808A66346347
12​	1808A373473		6.678​
13​	1808A373473		14.851​
14​	1808A373473		23.172​
15​	1808A373473		0.82​
16​	1808A373473		29.792​
17​	1808A373473		6.696​
18​	1808A373473
19​	1808A236236		4.692​
20​	1808A236236		36.031​
21​	1808A236236		79.404​
22​	1808A236236		84.109​
23​	1808A236236		39.471​
24​	1808A236236		12.239​
25​	1808A236236		5.045​
26​

Sheet: Sheet35

If you extend the part numbers to cells below (which is easy to do) you can use the following array formula in cell D2, to count parts that failed:

=SUM((IF(C5:C25>=50,1/COUNTIFS(A5:A25,A5:A25,C5:C25,">="&B2),0)))

How to fill blanks with part numbers:
https://www.techrepublic.com/blog/w...-fill-in-blank-cells-in-excel-quick-and-easy/