Countif multiple criteria met but if criteria met twice for duplicate value only count 1

[Chechel]

I just can't seem to write the formula for this one. I would appreciate assistance. I've tried to explain below what I want. Final result should be 3. I've stated criteria below - but the way I'm going about it could be wrong.
Can you help? Thank you.

ID	Part	OH	Usage		Result	Column	Count if
1	A	0	0		Don't Count	A	ID equals Same
1	B	0	0			B	Part equals A or B
2	A	1	0		Don't Count	C	OH equals 0 for both A and B
2	B	0	1			D	Usage is greater than 0 for either A or B
3	A	0	0		Count 1
3	B	0	1
4	A	0	1		Count 1
4	B	0	1
5	A	1	0		Don't Count
5	B	0	0
6	A	1	1		Don't Count
6	B	1	0
7	A	1	1		Don't Count
7	B	0	0
8	A	0	1		Don't Count
8	B	1	0
9	A	0	0		Count 1
9	B	0	1

 

[Automatrix]

I added two helper columns. The first one must be 0 and the next one above 0 to count, then divide by 2.

Book1
	A	B	C	D	E	F	G	H	I	J
1	ID	Part	OH	Usage	OHcalc	UsageCalc		Result	Column	Count if
2	1	A	0	0	0	0		Don't Count	A	ID equals Same
3	1	B	0	0	0	0			B	Part equals A or B
4	2	A	1	0	1	1		Don't Count	C	OH equals 0 for both A and B
5	2	B	0	1	1	1			D	Usage is greater than 0 for either A or B
6	3	A	0	0	0	1		Count 1
7	3	B	0	1	0	1				3
8	4	A	0	1	0	2		Count 1
9	4	B	0	1	0	2
10	5	A	1	0	1	0		Don't Count
11	5	B	0	0	1	0
12	6	A	1	1	2	1		Don't Count
13	6	B	1	0	2	1
14	7	A	1	1	1	1		Don't Count
15	7	B	0	0	1	1
16	8	A	0	1	1	1		Don't Count
17	8	B	1	0	1	1
18	9	A	0	0	0	1		Count 1
19	9	B	0	1	0	1
Sheet1

Cell Formulas
Range	Formula
E2:E19	E2	=SUMIFS($C$2:$C$19,$A$2:$A$19,A2)
F2:F19	F2	=SUMIFS($D$2:$D$19,$A$2:$A$19,A2)
J7	J7	=COUNTIFS(E2:E19,0,F2:F19,">"&0)/2

 

[Chechel]

Thank you for your help. I'm looking for a way to write the formula without helper columns. I'm using a separate worksheet to calculate a work order which has 8000 parts and about 600 parts that can be alternatives. The database I am using can be as much as 130000 lines long, - it's much larger than the sample I gave, and I cannot add helper columns to it. In this database there are way more than just two different parts.

 

[Automatrix]

This sounds like something that requires Power Query (where you can add "virtual" helper columns) or VBA, based on the high number of rows alone.

 

[Chechel]

Hello, I am trying to add the helper columns, but I need a way to pull out just part A or B among parts and exclude all other parts. Can you help?

 

[Automatrix]

You can filter for A and B in Power Query. I think it really is the best option for what you are trying to do, and much easier to learn than formulas.

 

[Chechel]

You can filter for A and B in Power Query. I think it really is the best option for what you are trying to do, and much easier to learn than formulas.

Thank you. I added 3 helper columns. I assigned a code to each part with alternatives. So alternatives have the same code. I added your two helper columns as well. Thank you for leading me in the right direction.