Count unique multiple values once with criteria

[Legacy 469343]

Hi! What formula do I need to count a PO once if all the items of one PO have been delivered (marked X)?

P.S.: Unfortunately I'm on my office PC, so XL2BB cannot be downloaded...

 

[Legacy 469343]

Sorry, based on my example, do you want a 2 in cell B2?

varios 19jun2021.xlsm
	A	B	C	D
1	Pos	Del Po
2	3	2
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
12	comp3	3	3	X
13	comp3	3	4	X
14	comp3	3	5	X
15	comp3	3	6	X
Hoja6

Sorry, based on my example, do you want a 2 in cell B2?

varios 19jun2021.xlsm
	A	B	C	D
1	Pos	Del Po
2	3	2
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
12	comp3	3	3	X
13	comp3	3	4	X
14	comp3	3	5	X
15	comp3	3	6	X
Hoja6

Yes that should be the outcome

 

[johnnyL]

What is C2 supposed to represent? A2 - B2 is my guess.

 

[johnnyL]

@Black Arrow is vba code an option?

 

[DanteAmor]

Yes that should be the outcome

Try this:

Dante Amor
	A	B	C	D
1	Pos	Del Po	Pen PO
2	6	4	2
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
12	comp3	3	3	X
13	comp3	3	4	X
14	comp3	3	5	X
15	comp3	3	6	X
16	comp4	4	1	X
17	comp4	4	2	X
18	comp5	5	1
19	comp6	6	1	X
20	comp6	6	2	X
Dante Amor

Cell Formulas
Range	Formula
A2	A2	=SUM(--(FREQUENCY(B4:B20,B4:B20)>0))
B2	B2	=A2-SUM(--(FREQUENCY(B4:B20,B4:B20)-FREQUENCY(IF(D4:D20="X",B4:B20),B4:B20)>0))
C2	C2	=A2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

With table1

Dante Amor
	A	B	C	D
1	Pos	Del Po	Pen PO
2	3	2	1
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
Dante Amor

Cell Formulas
Range	Formula
A2	A2	=SUM(--(FREQUENCY(Table1[PO],Table1[PO])>0))
B2	B2	=A2-SUM(--(FREQUENCY(Table1[PO],Table1[PO])-FREQUENCY(IF(Table1[Delivered]="X",Table1[PO]),Table1[PO])>0))
C2	C2	=A2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 469343]

Try this:

Dante Amor
	A	B	C	D
1	Pos	Del Po	Pen PO
2	6	4	2
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
12	comp3	3	3	X
13	comp3	3	4	X
14	comp3	3	5	X
15	comp3	3	6	X
16	comp4	4	1	X
17	comp4	4	2	X
18	comp5	5	1
19	comp6	6	1	X
20	comp6	6	2	X
Dante Amor

Cell Formulas
Range	Formula
A2	A2	=SUM(--(FREQUENCY(B4:B20,B4:B20)>0))
B2	B2	=A2-SUM(--(FREQUENCY(B4:B20,B4:B20)-FREQUENCY(IF(D4:D20="X",B4:B20),B4:B20)>0))
C2	C2	=A2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

With table1

Dante Amor
	A	B	C	D
1	Pos	Del Po	Pen PO
2	3	2	1
3	Company	PO	Item	Delivered
4	comp1	1	1	X
5	comp2	2	1	X
6	comp2	2	2	X
7	comp2	2	3	X
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X
11	comp3	3	2	X
Dante Amor

Cell Formulas
Range	Formula
A2	A2	=SUM(--(FREQUENCY(Table1[PO],Table1[PO])>0))
B2	B2	=A2-SUM(--(FREQUENCY(Table1[PO],Table1[PO])-FREQUENCY(IF(Table1[Delivered]="X",Table1[PO]),Table1[PO])>0))
C2	C2	=A2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

This did the trick! Thanks!

 

[DanteAmor]

Im glad to help you, thanks for the feedback

 

[DanteAmor]

This did the trick! Thanks!

=A2-SUM(--(FREQUENCY(Table1[PO],Table1[PO])-FREQUENCY(IF(Table1[Delivered]="X",Table1[PO]),Table1[PO])>0))

In fact, what the formula does is find the incomplete orders, so of the total orders minus the incomplete ones, we have the complete ones.

What formula do I need to count a PO once if all the items of one PO have been delivered (marked X)?

So if we change the focus to finding the incomplete ones (and we stop fighting for the complete ones, which is more complicated), the formula can be simplified like this:

=A2-SUM(--(FREQUENCY(IF(Table1[Delivered]="",Table1[PO]),Table1[PO])>0))

Press CTRL+SHIFT+ENTER to enter array formulas.