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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Peak % limits[/td][td]

50​

[/td][td]Parts failed[/td][td]

2​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]1808A66346347[/td][td][/td][td]

99.006​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]1808A66346347[/td][td][/td][td]

73.492​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]1808A66346347[/td][td][/td][td]

32.328​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]1808A66346347[/td][td][/td][td]

9.915​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]1808A66346347[/td][td][/td][td]

52.913​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]1808A66346347[/td][td][/td][td]

8.115​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]1808A66346347[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]1808A373473[/td][td][/td][td]

6.678​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]1808A373473[/td][td][/td][td]

14.851​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]1808A373473[/td][td][/td][td]

23.172​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]1808A373473[/td][td][/td][td]

0.82​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]1808A373473[/td][td][/td][td]

29.792​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]1808A373473[/td][td][/td][td]

6.696​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]1808A373473[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]1808A236236[/td][td][/td][td]

4.692​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]1808A236236[/td][td][/td][td]

36.031​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]1808A236236[/td][td][/td][td]

79.404​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]1808A236236[/td][td][/td][td]

84.109​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]1808A236236[/td][td][/td][td]

39.471​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]1808A236236[/td][td][/td][td]

12.239​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]1808A236236[/td][td][/td][td]

5.045​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet35[/td][/tr][/table]

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/