# of Orders that Shipped Out After the Order Date Excluding Duplicate Order #s

  • I'm trying to create a formula that will tell me the number of orders that shipped after the order date. I was able to find a formula that spit out for me that there are 14 rows greater than 3/1/2019 but I need it to spit out that there are actually just 9 orders that are greater than B2 due to the duplicate order numbers in Column A. Anybody able to help? I don't know the right questions to ask Google to help me formulate this complicated calculation.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 84"]Order Nbr[/TD]
    [TD="width: 90"]Order Date[/TD]
    [TD="width: 132"]Ship Date[/TD]

    [/tr]


    [tr]


    [td]

    1647844

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:21[/TD]

    [/tr]


    [tr]


    [td]

    1647844

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:21[/TD]

    [/tr]


    [tr]


    [td]

    1648106

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:03[/TD]

    [/tr]


    [tr]


    [td]

    1648067

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:01[/TD]

    [/tr]


    [tr]


    [td]

    1648067

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:01[/TD]

    [/tr]


    [tr]


    [td]

    1648067

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:01[/TD]

    [/tr]


    [tr]


    [td]

    1648028

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 9:59[/TD]

    [/tr]


    [tr]


    [td]

    1648003

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:06[/TD]

    [/tr]


    [tr]


    [td]

    1648003

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:06[/TD]

    [/tr]


    [tr]


    [td]

    1647930

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:14[/TD]

    [/tr]


    [tr]


    [td]

    1647919

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:23[/TD]

    [/tr]


    [tr]


    [td]

    1647919

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:23[/TD]

    [/tr]


    [tr]


    [td]

    1647830

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:18[/TD]

    [/tr]


    [tr]


    [td]

    1647815

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/4/19 10:11[/TD]

    [/tr]


    [tr]


    [td]

    1647670

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:18[/TD]

    [/tr]


    [tr]


    [td]

    1647669

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:20[/TD]

    [/tr]


    [tr]


    [td]

    1647655

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:21[/TD]

    [/tr]


    [tr]


    [td]

    1647624

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:24[/TD]

    [/tr]


    [tr]


    [td]

    1647624

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:24[/TD]

    [/tr]


    [tr]


    [td]

    1647593

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:25[/TD]

    [/tr]


    [tr]


    [td]

    1647575

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:17[/TD]

    [/tr]


    [tr]


    [td]

    1647573

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:27[/TD]

    [/tr]


    [tr]


    [td]

    1647566

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:29[/TD]

    [/tr]


    [tr]


    [td]

    1647537

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:30[/TD]

    [/tr]


    [tr]


    [td]

    1647498

    [/td]


    [TD="align: right"]3/1/2019[/TD]
    [TD="align: right"]3/1/19 15:28[/TD]

    [/tr]


    [/TABLE]

  • Hello,


    Your question is


    Quote

    the number of orders that shipped after the order date


    My initial reaction is 100% ... orders can only be shipped after the Order date ... no ???:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Let's move on ... :smile:


    More seriously ...


    With your data in range A1:C26, you could test say in cell E2 the following Array formula :


    Code
    1. =SUM(IF(FREQUENCY(IF(INT($C$2:$C$26)>$B$2,IF($A$2:$A$26<>"",MATCH($A$2:$A$26,$A$2:$A$26,0))),ROW($A$2:$A$26)-ROW($A$2)+1),1))


    Hope this will help :wink:


    P.S. Once tested out ... let me know the result of your " Accounting Controls " ..... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Again - thanks for the laugh! :) You eased my frustration level.


    When you used that formula, did you get the result of 9? I cut-n-pasted that formula into cell E2 and got 1. So I dragged the formula down to E26 - got 1's on all orders shipped on 3/4 and #VALUE! on all orders shipped on 3/1. So I added up the 1's and got 14. That's the same result my COUNTIF formula was giving me. I'm trying to get a formula that will spit me out the correct answer of 9 for this example. There are 9 unique order numbers from the list that shipped out on 3/4.

  • Try this in Column "E" : it will place the word, "Distinct" if the result is Distinct. On the rows with the same date where you now get "#VALUE" you will now have, "FALSE". You can use COUNTIF to tally up the "Distinct. I hope this helps you. I did not try COUNTIF but if you need a number you you can use this formula: =IF(E2="Distinct",1,"") and copy that formula down your page. That will leave cells marked "FALSE" as blank so COUNTIF will work.


    =IF(INT(C15)>INT(B15),IF(COUNTIF($A$2:$A15,$A15)=1,"Distinct",""))


    Regards...George

  • A slightly improved fix: =IF(INT(C2)>INT(B2),IF(COUNTIF($A$2:$A2,$A2)=1,1,"")) but you are still left with "FALSE" on the items ordered and shipped on the same day. Maybe somebody else can add to my work and create a fix for you.


    Regards...George
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 282"]

    [tr]


    [TD="width: 69"]Order Nbr[/TD]
    [TD="width: 75"]Order Date[/TD]
    [TD="width: 104"]Ship Date[/TD]
    [TD="width: 64"] [/TD]
    [TD="class: xl66, width: 64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647844[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:21[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647844[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:21[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648106[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:03[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648028[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 9:59[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648003[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:06[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648003[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:06[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647930[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:14[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647919[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:23[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647919[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:23[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647830[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:18[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647815[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:11[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647670[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:18[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647669[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:20[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647655[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:21[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647624[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:24[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647624[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:24[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647593[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:25[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647575[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:17[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647573[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:27[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647566[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:29[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647537[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:30[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647498[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:28[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [/TABLE]

  • Quote

    Again - thanks for the laugh! :) You eased my frustration level.


    When you used that formula, did you get the result of 9 ?


    Love your sense of humor ... !!! :facepull:


    Whenever you are confronted with an Array formula ...:heelloo:


    Instead of the standard Enter key ...


    you need to use simultaneously the three keys : Control Shift Enter


    Excel will validate your entry by inserting for you a couple of curly brackets at the beginning and at the end of the formula : { and }


    Have a go ... it is Fun ...:thumbcoo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Attached is your file for a visual comparison ... :smile:


    The advantage of the Array formula over the step-by-step approach ...


    All calculations (three steps in your two columns ...) are condensed into a one single cell ... :wink:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks, Karim. I am a 71 year old retired former factory worker. Everything I do know in MS Excel and other computer usage is self taught. I am always open to learn more.


    Regards..George

  • Thanks, Karim. I am a 71 year old retired former factory worker. Everything I do know in MS Excel and other computer usage is self taught. I am always open to learn more.


    Regards..George


    Glad to hear you are picking every opportunity to progress ... :smile:


    Keep up the energy and the curiosity of your youth ... :jumpupdo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sincerely hope our favorite Acctg Controller has not been discouraged ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yes, she went quiet all of a sudden.
    Carim, I have been thinking about your solution. While it certainly does look impressive it does have a drawback that my rather crude solution does not have. You have locked it to 26 rows. If the Acctg Controller increases her number if rows your solution will not function whereas with my solution she need only to increase the =Sum(xx:xxxx) to whatever range is required.


    George

  • Hello,


    Since you seem to be extremely eager to solve Acctg Controller 's question ... :wink:


    Attached is an improved Version 2 ... which does NOT require your two columns to be copied down nor to adjust the Sum formula for the exact number of rows ...


    Indeed ... the new Array formula adjusts itself automatically to the exact number of rows in Column C ...


    Hope this answers your concern ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim's formula does not have to be limited to 26 rows


    =SUM(IF(FREQUENCY(IF(INT($C$2:$C$100000)>$B$2,IF($A$2:$A$100000<>"",MATCH($A$2:$A$100000,$A$2:$A$100000,0))),ROW($A$2:$A$100000)-ROW($A$2)+1),1))


    as an array formula will work for up to 99,999 rows of data (plus header row), the formula would be slightly slower due to the extra rows being calculated by the array formula, but the result is the same (the additional blank cells will not affect the result), the 100000 can be changed to reflect the maximum possible or likely rows of data that the sheet may include in the future.


    The exact number of rows to be calculated could be determined by incorporating COUNTA(A:A) into the formula using INDIRECT.


    Formulas are not my strong point so I will leave it up to Carim to get the incorporation of INDIRECT(........COUNTA(A:A)) working properly!!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • LOL Carim, as I said formulas not my strong point, as I was trying to get INDIRECT & COUNTA to work you came up with the much betterr OFFSET & MATCH solution.


    Very neat formula! :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you both, Carim and KjBox. You have both, in a well natured, and kind way, shown me my ignorance.


    I just hope AcctgContoller comes back to benefit from this in interchange. She now has a choice of solutions. :congrats:



    Regards...George

  • [USER="33159"]KjBox[/USER] Thanks a lot for your nice compliment ... :smile:



    [USER="333742"]George-1947[/USER] Excel does offer to each one of us ... a constant opportunity to challenge ourselves ...


    a never-ending learning process ... !!! :wink:



    [USER="333778"]AcctgController[/USER] Do you realize how many men are eager ... to help you out ... :dance:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)