Count delivered PO's

[Legacy 469343]

Hi, how do I count the delivered PO's? A PO should only be counted if ALL line items of a PO have "Complete" in the Delivered column.

Test.xlsx
	A	B	C	D	E	F	G
1	WEEKS & DAYS		PO's	Del. PO's	Pen. PO's
2	Date	10-sep-21	5		5
3	Year	2021
4	Week #	36
5	Date (Mon)	6-9-2021
6	Date (Fri)	10-9-2021
7	Info		PO
8	Trigram	Project	Supplier	PO Sent	PO	Line item	Delivered
9	ABC	1	Company 1	16-Aug-19	PO-123456	1	Pending
10	ABC	1	Company 3	6-Aug-19	PO-123457	1	Complete
11	ABC	1	Company 3	6-Aug-19	PO-123457	2	Pending
12	ABC	1	Company 3	6-Aug-19	PO-123457	3	Pending
13	ABC	AAA	Company 1	2-Aug-19	PO-123458	1	Complete
14	ABC	1	Company 4	9-Aug-19	PO-123459	1	Complete
15	ABC	BBB	Company 2	19-Oct-20	PO-123460	1	Complete
16	ABC	BBB	Company 2	19-Oct-20	PO-123460	2	Complete
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TODAY()
C2	C2	=SUM(IF(FREQUENCY(MATCH(Table1[PO],Table1[PO],0),MATCH(Table1[PO],Table1[PO],0))>0,1))
E2	E2	=C2-D2
B5	B5	=IF(ISOWEEKNUM(DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)+1)>1,DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)+1+$B$4*7,DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)-6+$B$4*7)
B6	B6	=IF(ISOWEEKNUM(DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)+1)>1,DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)+5+$B$4*7,DATE($B$3,1,1)-WEEKDAY(DATE($B$3,1,1),2)+5+$B$4*7)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 469343]

Which column will have blanks?

Hi Fluff, column PO.

 

[Fluff]

Ok, change the helper column to

=IF([@PO]<>"",COUNTIFS([PO],[@PO],[Delivered],"Complete")=COUNTIFS([PO],[@PO]))

 

[Legacy 469343]

Which column will have blanks?

Column E, (PO's)

 

[Fluff]

You've already said that. Have a look at post#12