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

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:

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 :

=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 " ..... !!!

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"]

[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]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66"] [/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66"] [/TD]

[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="class: xl66"] [/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66"] [/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66"] [/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66, align: right"]1[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[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="class: xl66, align: center"]FALSE[/TD]

[/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:

Yes, the Table did not paste correctly. I did not recognise it as being an Array.

The updated file I added does work.

Regards...George

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:

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

Quote from George-1947

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:

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

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

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

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!

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:

Acknowledged. :ole:

George