Count delivered PO's

  • Thread starter Thread starter Legacy 469343
  • Start date Start date
L

Legacy 469343

Guest
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
ABCDEFG
1WEEKS & DAYSPO'sDel. PO'sPen. PO's
2Date10-sep-2155
3Year2021
4Week #36
5Date (Mon)6-9-2021
6Date (Fri)10-9-2021
7InfoPO
8TrigramProjectSupplierPO SentPOLine itemDelivered
9ABC1Company 116-Aug-19PO-1234561Pending
10ABC1Company 36-Aug-19PO-1234571Complete
11ABC1Company 36-Aug-19PO-1234572Pending
12ABC1Company 36-Aug-19PO-1234573Pending
13ABCAAACompany 12-Aug-19PO-1234581Complete
14ABC1Company 49-Aug-19PO-1234591Complete
15ABCBBBCompany 219-Oct-20PO-1234601Complete
16ABCBBBCompany 219-Oct-20PO-1234602Complete
Sheet1
Cell Formulas
RangeFormula
B2B2=TODAY()
C2C2=SUM(IF(FREQUENCY(MATCH(Table1[PO],Table1[PO],0),MATCH(Table1[PO],Table1[PO],0))>0,1))
E2E2=C2-D2
B5B5=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)
B6B6=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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Please check this

Book1
ABCDEFGHIJKLMNOPQR
1WEEKS & DAYSPO'sDel. PO'sPen. PO's
2Date10-09-212
3Year2021
4Week #36
5Date (Mon)6-Sep-21
6Date (Fri)10-Sep-21
7InfoPO
8TrigramProjectSupplierPO SentPOLine itemDeliveredUnique12345
9ABC1Company 116-Aug-19PO-123456PO-123456-11PendingPO-123456Pending    01 
10ABC1Company 36-Aug-19PO-123457PO-123457-11CompletePO-123457CompletePendingPending  12 
11ABC1Company 36-Aug-19PO-123457PO-123457-22PendingPO-123458Complete    101
12ABC1Company 36-Aug-19PO-123457PO-123457-33PendingPO-123459Complete    101
13ABCAAACompany 12-Aug-19PO-123458PO-123458-11CompletePO-123460CompleteComplete   20 
14ABC1Company 49-Aug-19PO-123459PO-123459-11Complete      2
15ABCBBBCompany 219-Oct-20PO-123460PO-123460-11Complete      
16ABCBBBCompany 219-Oct-20PO-123460PO-123460-22Complete      
Sheet1
Cell Formulas
RangeFormula
B2B2=TODAY()
C2C2=R14
B5B5=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)
B6B6=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)
J9:J16J9=IFERROR(INDEX($E$9:$E$16,SMALL(IF(FREQUENCY(IF($E$9:$E$16<>"",MATCH($E$9:$E$16,$E$9:$E$16,0)),ROW($E$9:$E$16)-ROW($E$9)+1),ROW($E$9:$E$16)-ROW($E$9)+1),ROWS(J$9:J9))),"")
K9:O16K9=IFERROR(VLOOKUP($J9&"-"&K$8,$F$9:$H$16,3,0),"")
P9:P13P9=COUNTIF(K9:O9,"Complete")
Q9:Q13Q9=COUNTIF($K9:$O9,"Pending")
R9:R13R9=IF(AND(P9=1,Q9=0),1,"")
R14R14=SUM(R9:R13)
F9:F16F9=E9&"-"&COUNTIF(E$9:E9,E9)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9:F16Cell ValueduplicatestextNO
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGH
1WEEKS & DAYSPO'sDel. PO'sPen. PO's
2Date10/09/2021532
3Year2021
4Week #36
5Date (Mon)44445
6Date (Fri)44449
7InfoPO
8TrigramProjectSupplierPO SentPOLine itemDeliveredColumn1
9ABC1Company 143693PO-1234561PendingFALSE
10ABC1Company 343683PO-1234571CompleteFALSE
11ABC1Company 343683PO-1234572PendingFALSE
12ABC1Company 343683PO-1234573PendingFALSE
13ABCAAACompany 143679PO-1234581CompleteTRUE
14ABC1Company 443686PO-1234591CompleteTRUE
15ABCBBBCompany 244123PO-1234601CompleteTRUE
16ABCBBBCompany 244123PO-1234602CompleteTRUE
Lists
Cell Formulas
RangeFormula
B2B2=TODAY()
C2C2=SUM(IF(FREQUENCY(MATCH(Table1[PO],Table1[PO],0),MATCH(Table1[PO],Table1[PO],0))>0,1))
D2D2=SUM(IF(FREQUENCY(IF(Table1[Column1],MATCH(Table1[PO],Table1[PO],0)),MATCH(Table1[PO],Table1[PO],0))>0,1))
E2E2=C2-D2
B5B5=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)
B6B6=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)
H9:H16H9=COUNTIFS([PO],[@PO],[Delivered],"Complete")=COUNTIFS([PO],[@PO])
 
Upvote 0
Solution
Ple
Fluff said:
How about
+Fluff 1.xlsm
ABCDEFGH
1WEEKS & DAYSPO'sDel. PO'sPen. PO's
2Date10/09/2021532
3Year2021
4Week #36
5Date (Mon)44445
6Date (Fri)44449
7InfoPO
8TrigramProjectSupplierPO SentPOLine itemDeliveredColumn1
9ABC1Company 143693PO-1234561PendingFALSE
10ABC1Company 343683PO-1234571CompleteFALSE
11ABC1Company 343683PO-1234572PendingFALSE
12ABC1Company 343683PO-1234573PendingFALSE
13ABCAAACompany 143679PO-1234581CompleteTRUE
14ABC1Company 443686PO-1234591CompleteTRUE
15ABCBBBCompany 244123PO-1234601CompleteTRUE
16ABCBBBCompany 244123PO-1234602CompleteTRUE
Lists
Cell Formulas
RangeFormula
B2B2=TODAY()
C2C2=SUM(IF(FREQUENCY(MATCH(Table1[PO],Table1[PO],0),MATCH(Table1[PO],Table1[PO],0))>0,1))
D2D2=SUM(IF(FREQUENCY(IF(Table1[Column1],MATCH(Table1[PO],Table1[PO],0)),MATCH(Table1[PO],Table1[PO],0))>0,1))
E2E2=C2-D2
B5B5=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)
B6B6=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)
H9:H16H9=COUNTIFS([PO],[@PO],[Delivered],"Complete")=COUNTIFS([PO],[@PO])
Click to expand...
Please clarifiy what does this function do

=SUM(IF(FREQUENCY(MATCH(Table1[PO],Table1[PO],0),MATCH(Table1[PO],Table1[PO],0))>0,1))
 
Upvote 0
It counts the number of distinct POs
 
Upvote 0
Fluff said:
It counts the number of distinct POs
Click to expand...
Hi Fluff,

Is it possible to combine =SUM(IF(FREQUENCY(IF(Table1[Column1],MATCH(Table1[PO],Table1[PO],0)),MATCH(Table1[PO],Table1[PO],0))>0,1)) and =COUNTIFS([PO],[@PO],[Delivered],"Complete")=COUNTIFS([PO],[@PO])

Thanks!
 
Upvote 0
Possibly, but I don't know how.
 
Upvote 0
Which column will have blanks?
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
How to count the number of numbers in a given period?
2
Replies
16
Views
304
JenniferMurphy
JenniferMurphy
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
127
Nicha
N
A
  • Solved
SUMPRODUCT Help
Replies
5
Views
145
AYouQueTai
A
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
99
aurelius33
A
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
191
Fluff
Fluff

Forum statistics

Threads
1,224,587
Messages
6,179,738
Members
452,940
Latest member
Lawrenceiow

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top