Checking a number of measurements against constantly changing high and low tolerences.

[Ishcor]

In my work I take around 100 different measurements. Each measurement has it's own spec for high and low tolerance. For example measurement #1 can be between 0 and 1. Measurement #4 could be between 0 and 1.5 and so on. I use conditional formatting to highlight the measurements that are out of spec, but then I have to manually count each highlighted cell to give an overall report. I'm wondering it I could automate the process to give me a total count of each out of spec measurement. I'm wondering if anyone has suggestions on how I can do that. I have an example below on how the spreadsheet is organized. Keep in mind that I can't reorganize any of the measurements and this is only a small example of the 100 measurements that are done.

Reading

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="width:48pt" width="64" span="14"> </colgroup><tbody>
[TD="class: xl64, width: 64"]Measurement number

---

[/TD]
[TD="class: xl64, width: 85"] [/TD]
[TD="class: xl65, width: 64, align: right"] #1
[/TD]
[TD="class: xl65, width: 64, align: right"]#2
[/TD]
[TD="class: xl65, width: 64, align: right"]#3
[/TD]
[TD="class: xl65, width: 64, align: right"] #4
[/TD]
[TD="class: xl65, width: 64, align: right"]#5
[/TD]
[TD="class: xl65, width: 64, align: right"]#6
[/TD]
[TD="class: xl65, width: 64, align: right"]#7
[/TD]
[TD="class: xl66, width: 64, align: right"]#8
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="colspan: 2"]High tolerance[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="colspan: 2"]Low tolerance[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]1.1
[/TD]
[TD="align: right"]0.1[/TD]
[TD="class: xl63, align: right"]3.1[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]

[TD="colspan: 2"]Count out of spec[/TD]
[TD="align: right"]1
[/TD]

</tbody>

 

[JoeMo]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Measurement number	#1	#2	#3	#4	#5	#6	#7	#8	Out of Spec
2	High tolerance	1	1	1	1.5	3.5	1	2	1
3	Low tolerance	0.1	0.1	0.1	0	0	0	0	0
4
5	Reading1	0.5	0.2	1.1	0.1	3.1	1	1.5	0.5	1
6	Reading2	0.3	0.05	0.8	0	3.7	1.2	1.9	1.3	4
Sheet1