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...

 

[DanteAmor]

Try this:

varios 19jun2021.xlsm
	A	B	C	D	E
3	Company	PO	Item	Delivered	Complete
4	comp1	1	1	X	Yes
5	comp2	2	1	X	No
6	comp2	2	2	X
7	comp2	2	3
8	comp2	2	4
9	comp2	2	5
10	comp3	3	1	X	Yes
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

Cell Formulas
Range	Formula
E4:E15	E4	=IF(COUNTIF($B$4:$B$15,B4)=COUNTIF(B4:$B$15,B4),IF(COUNTIF($B$4:$B$15,B4)=COUNTIFS($B$4:$B$15,B4,$D$4:$D$15,"X"),"Yes","No"),"")

 

[Legacy 469343]

I came up with this formula: =SUM(--(FREQUENCY(IF(Table1[Delivered]="X",MATCH(Table1[PO],Table1[PO],0)),ROW(Table1[PO])-ROW(B4)+1)>0))

But this counts a PO once if one item has a X behind it. I want it only to count if ALL items have an X behind it

 

[johnnyL]

@Black Arrow Did you try @DanteAmor's formula?

You will want to substitute your 'Table1[Delivered]' and 'Table1[PO]' into it.

 

[Legacy 469343]

@Black Arrow Did you try @DanteAmor's formula?

Not yet, but I know it’s possible without the additional yes / no column

 

[DanteAmor]

Not yet, but I know it’s possible without the additional yes / no column

Maybe if you explain better what is the result you want and where you want it.

 

[Legacy 469343]

Maybe if you explain better what is the result you want and where you want it.

For example: If items 1,2,3,4,5,6 of Company 2 have an X behind it. I want it to count as one PO fully delivered

 

[johnnyL]

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

What do you mean by 'count'? Where will this 'count' go?

 

[Legacy 469343]

What do you mean by 'count'? Where will this 'count' go?

There are 3 companies, all with one PO number, but all with different item amounts. So if all items would have an X behind them, 3 PO’s need to be the outcome

 

[DanteAmor]

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