Count if unique combination

[Danielv]

Hi guys,
could really use some help creating a complicated Indices file.
i am attempting to count the number of setup events for a given machine.
my Indices are setup in one tab, and all the data in a second tab.
the data tab is built as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Machine[/TD]
[TD]Date[/TD]
[TD]Order Number[/TD]
[TD]Event[/TD]
[TD].......[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

usual this would be simple counting the number of "Setup" events for any Given Machine, however i have duplicates due to improper data input or workers taking a break during a setup (event number 5) and the resuming a setup.

I need to be able to count the number of "2" events corresponding to two unique identifiers, "Order Number" and "Machine".
keep in mind these are large files with several thousand entry's....

for example: (Filtered for machine AM-21)
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]AM-21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A98771[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]AM-21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A98771[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A98736[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A97202[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A97202[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A97202[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="width: 113, align: left"]A97202[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD]A094449[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD]A094449[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]AM-21[/TD]
[TD]27/10/2014[/TD]
[TD]A094449[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
(1-Work, 2-SU, 5-Break)

in this example only the highlighted rows should be counted (3).
Also counting the number of unique Order numbers is not a good indicate as some orders don't have setups.

 

[Oeldere]

If I read your question well, with a pivot table.

Book1
	A	B	C	D	E	F	G	H
1	AM	date	job	count	criteria		criteria	yes
2	AM-21	27-10-2014	A98771	1	yes
3	AM-21	27-10-2014	A98736	1	yes		Rijlabels	Telling van count
4	AM-21	27-10-2014	A97202	1	yes		A094449	1
5	AM-21	27-10-2014	A97202	2	NO		A97202	2
6	AM-21	27-10-2014	A97202	1	yes		A98736	1
7	AM-21	27-10-2014	A094449	5	yes		A98771	1
8	AM-21	27-10-2014	A094449	2	NO		Eindtotaal	5
Blad8

Cell Formulas
Range	Formula
E2	=IF(D2=2,"NO","yes")
E3	=IF(D3=2,"NO","yes")
E4	=IF(D4=2,"NO","yes")
E5	=IF(D5=2,"NO","yes")
E6	=IF(D6=2,"NO","yes")
E7	=IF(D7=2,"NO","yes")
E8	=IF(D8=2,"NO","yes")