Counting Unique Values and also Sums in a specified date range.

[Davereau]

I utilize the below table to access various sales data. I have a separate file to calculate unique orders and totaling sales per month (each month has it's own tab). I copy this information from a master sales data file. What I would like to do is grab that information from the master file instead of copying each months worth data to a new file. For example I use this formula to see how many orders that are in that month. Some have multiple line items but it only counts that sales order number once.

=SUMPRODUCT((A3:A135<>"")/COUNTIF(A3:A135,A3:A135&""))

In the above example, the number of rows are fixed for the certain month i would be calculating.

So in the above formula example, I am trying to find out how many unique So_Nbr's there are but would like to use a date range with the dates in the Taken_Date column. There is more that I am trying to do, but I think if I can figure this out the rest should fall in line.

I have deleted information from the "Name" and "Desc" columns to protect the innocent.

I understand i may not have explained well so let me know if you have any questions.

Thanks in advance.

Dave

So_Nbr Cust_Nbr Name Cust_Po_Nbr Taken_Date ReqdDate Shipped_Date Part_Nbr Tot_Qty Unit_Price Tot_Value 1021576 0200079 11/09/2016 12/06/2016 Not Shipped 900955 8 $ 115.26 $ 922.08 1021575 1100048 11/09/2016 12/06/2016 Not Shipped 905645 12 $ 33.38 $ 400.56 1021574 0700020 11/09/2016 12/20/2016 Not Shipped 907239 2 $ 63.54 $ 127.08 1021573 2000009 11/09/2016 12/08/2016 Not Shipped 904617 54 $ 45.44 $ 2,453.76 1021572 0900060 11/09/2016 12/06/2016 Not Shipped 900821 4 $ 175.56 $ 702.24 1021571 0300021 11/09/2016 12/06/2016 Not Shipped 900955 8 $ 115.26 $ 922.08 1021570 1400046 11/08/2016 12/06/2016 Not Shipped 900957 8 $ 157.03 $ 1,256.24 1021569 2200001 11/08/2016 12/06/2016 Not Shipped 901446 4 $ 57.19 $ 228.76 1021568 1900011 11/08/2016 12/06/2016 Not Shipped 903389 800 $ 1.41 $ 1,128.00 1021567 0400007 11/08/2016 12/06/2016 Not Shipped 907147 24 $ 50.00 $ 1,200.00 1021566 0400007 11/08/2016 12/05/2016 Not Shipped 904991 60 $ 33.08 $ 1,984.80 1021565 0400007 11/07/2016 12/05/2016 Not Shipped 907183 4 $ 161.30 $ 645.20 1021564 0200002 11/07/2016 12/07/2016 Not Shipped 902905 100 $ 34.39 $ 3,439.00 1021563 1300018 11/07/2016 12/05/2016 Not Shipped 903819 3 $ 333.08 $ 999.24 1021562 1300087 11/07/2016 08/14/2017 Not Shipped 906924 40 $ 42.00 $ 1,680.00 1021561 1300087 11/07/2016 04/28/2017 Not Shipped 906924 40 $ 42.00 $ 1,680.00 1021560 1300087 11/07/2016 01/09/2017 Not Shipped 906924 40 $ 42.00 $ 1,680.00 1021559 0400007 11/07/2016 12/01/2016 Not Shipped 907147 18 $ 50.00 $ 900.00 1021558 0400007 11/07/2016 11/29/2016 Not Shipped 907148 6 $ 33.00 $ 198.00 1021557 0400055 11/07/2016 11/30/2016 Not Shipped 906941 20 $ 126.49 $ 2,529.80 1021557 0400055 11/07/2016 11/30/2016 Not Shipped 903138 16 $ 217.64 $ 3,482.24 1021556 2200051 11/07/2016 12/02/2016 Not Shipped 903688 94 $ 85.00 $ 7,990.00 1021555 2000041 11/07/2016 01/18/2017 Not Shipped 901111 91 $ 65.41 $ 5,952.31 1021554 2000041 11/07/2016 01/18/2017 Not Shipped 901111 53 $ 65.41 $ 3,466.73 1021553 2000041 11/07/2016 12/30/2016 Not Shipped 901106 156 $ 26.88 $ 4,193.28 1021552 0900001 11/07/2016 11/14/2016 11/08/2016 900681 16 $ 4.61 $ 73.76 1021551 2200001 11/07/2016 12/02/2016 Not Shipped 900852 4 $ 62.35 $ 249.40 1021550 1600003 11/07/2016 12/02/2016 Not Shipped 903607 30 $ 25.00 $ 750.00 1021550 1600003 11/07/2016 12/02/2016 Not Shipped 905181 30 $ 50.00 $ 1,500.00 1021549 1600003 11/07/2016 12/02/2016 Not Shipped 905381 50 $ 31.59 $ 1,579.50 1021548 0800065 11/04/2016 12/02/2016 Not Shipped 900703 4 $ 77.55 $ 310.20 1021547 0100184 11/04/2016 12/01/2016 Not Shipped 900685 4 $ 62.79 $ 251.16 1021546 0900011 11/04/2016 11/29/2016 Not Shipped 900655 6 $ 72.60 $ 435.60 1021545 2000009 11/04/2016 12/23/2016 Not Shipped 903626 24 $ 116.00 $ 2,784.00 1021544 0300021 11/04/2016 12/01/2016 Not Shipped 901446 20 $ 54.98 $ 1,099.60 1021543 0600004 11/02/2016 11/30/2016 Not Shipped 901445 8 $ 43.91 $ 351.28 1021542 0100183 11/02/2016 12/05/2016 Not Shipped 901100 153 $ 91.39 $ 13,982.67 1021541 0200002 11/02/2016 12/13/2016 Not Shipped 902905 50 $ 34.39 $ 1,719.50 1021540 1300087 11/02/2016 06/05/2017 Not Shipped 906925 36 $ 45.00 $ 1,620.00 1021539 1300087 11/02/2016 04/03/2017 Not Shipped 906925 36 $ 45.00 $ 1,620.00 1021538 0400053 11/01/2016 11/28/2016 Not Shipped 902493 1 $ 66.30 $ 66.30 1021538 0400053 11/01/2016 11/28/2016 Not Shipped 900684 10 $ 10.80 $ 108.00 1021538 0400053 11/01/2016 11/28/2016 Not Shipped 900915 2 $ 97.77 $ 195.54 1021537 0900011 10/31/2016 11/21/2016 Not Shipped 900656 2 $ 70.18 $ 140.36 1021536 1300031 10/31/2016 10/31/2016 10/31/2016 900672 1 $ 117.36 $ 117.36 1021535 0400007 10/31/2016 10/31/2016 11/03/2016 907149 25 $ 45.00 $ 1,125.00 1021534 0600003 10/29/2016 11/29/2016 11/04/2016 902153 8 $ 126.00 $ 1,008.00 1021533 0900011 10/29/2016 11/29/2016 Not Shipped 904437 20 $ 3.95 $ 79.00 1021532 2000009 10/29/2016 11/29/2016 Not Shipped 903622 50 $ 18.50 $ 925.00 1021531 0900059 10/29/2016 11/28/2016 Not Shipped 902906 5 $ 76.09 $ 380.45 1021530 1300004 10/29/2016 11/30/2016 Not Shipped 900820 8 $ 126.65 $ 1,013.20 1021529 0600004 10/28/2016 11/29/2016 Not Shipped 901041 8 $ 41.33 $ 330.64 1021527 2000009 10/27/2016 11/28/2016 Not Shipped 903538 224 $ 34.66 $ 7,763.84 1021527 2000009 10/27/2016 11/28/2016 Not Shipped 903920 2 $ 32.17 $ 64.34 1021526 1400046 10/26/2016 11/23/2016 Not Shipped 900957 8 $ 157.03 $ 1,256.24 1021525 0900002 10/26/2016 11/23/2016 Not Shipped 902884 1 $ 403.22 $ 403.22 1021524 0400007 10/26/2016 11/23/2016 Not Shipped 907183 50 $ 123.29 $ 6,164.50 1021523 0400007 10/26/2016 11/23/2016 Not Shipped 907180 54 $ 123.29 $ 6,657.66 1021523 0400007 10/26/2016 11/23/2016 Not Shipped 904991 12 $ 42.28 $ 507.36 1021522 0100020 10/26/2016 11/23/2016 Not Shipped 902078 4 $ 118.51 $ 474.04 1021521 0100020 10/26/2016 11/23/2016 Not Shipped 905995 12 $ 45.39 $ 544.68 1021520 0900031 10/26/2016 11/30/2016 Not Shipped 907923 45 $ 140.57 $ 6,325.65 1021519 1100010 10/26/2016 11/22/2016 Not Shipped 904274 6 $ 83.74 $ 502.44 1021518 2200001 10/26/2016 11/22/2016 Not Shipped 905707 3 $ 158.08 $ 474.24 1021517 0600004 10/26/2016 11/23/2016 Not Shipped 900914 66 $ 67.62 $ 4,462.92 1021516 0500079 10/26/2016 11/22/2016 11/01/2016 906942 20 $ 135.93 $ 2,718.60 1021516 0500079 10/26/2016 11/22/2016 11/01/2016 901320 20 $ 59.83 $ 1,196.60 1021515 1300018 10/25/2016 11/22/2016 Not Shipped 903821 6 $ 125.70 $ 754.20 1021514 0100183 10/25/2016 12/02/2016 Not Shipped 906900 138 $ 73.99 $ 10,210.62 1021513 1900011 10/25/2016 11/22/2016 Not Shipped 900956 8 $ 165.91 $ 1,327.28 1021512 0200002 10/19/2016 11/21/2016 Not Shipped 902906 3 $ 53.09 $ 159.27 1021511 0700020 10/24/2016 12/20/2016 Not Shipped 907239 18 $ 63.54 $ 1,143.72 1021510 0400050 10/24/2016 11/21/2016 Not Shipped 907151 5 $ 54.76 $ 273.80 1021509 0700003 10/24/2016 05/02/2017 Not Shipped 900300 8 $ 121.94 $ 975.52 1021509 0700003 10/24/2016 05/02/2017 Not Shipped 900326 12 $ 60.13 $ 721.56 1021508 1800007 10/24/2016 11/18/2016 Not Shipped 904835 3 $ 156.06 $ 468.18 1021507 0400050 10/23/2016 11/18/2016 11/04/2016 907151 20 $ 54.76 $ 1,095.20 1021506 0100160 10/23/2016 11/18/2016 11/08/2016 901447 6 $ 57.04 $ 342.24 1021505 2200053 10/23/2016 11/18/2016 11/08/2016 906938 3 $ 88.35 $ 265.05 1021504 0300021 10/23/2016 11/17/2016 Not Shipped 901446 36 $ 54.98 $ 1,979.28 1021503 0700058 10/23/2016 11/17/2016 11/02/2016 900915 6 $ 80.65 $ 483.90 1021502 0300076 10/21/2016 10/24/2016 10/21/2016 900204 316 $ - $ - 1021501 0700008 10/20/2016 11/16/2016 11/08/2016 904275 2 $ 155.19 $ 310.38 1021500 2000009 10/20/2016 11/16/2016 Not Shipped 903669 52 $ 35.63 $ 1,852.76 1021499 2100002 10/19/2016 10/20/2016 10/19/2016 907274 2 $ - $ - 1021498 2000009 10/19/2016 11/17/2016 Not Shipped 903538 224 $ 34.66 $ 7,763.84 1021497 0400007 10/19/2016 11/16/2016 10/24/2016 907148 6 $ 33.00 $ 198.00 1021496 1800032 10/19/2016 01/13/2017 Not Shipped 906423 40 $ 60.00 $ 2,400.00 1021495 2200051 10/18/2016 11/25/2016 Not Shipped 903846 63 $ 43.77 $ 2,757.51 1021494 1300146 10/18/2016 11/15/2016 11/03/2016 904979 6 $ 155.89 $ 935.34 1021493 1400046 10/18/2016 11/15/2016 10/26/2016 900957 16 $ 157.03 $ 2,512.48 1021492 0100001 10/17/2016 10/28/2016 10/20/2016 800000 1 $ - $ - 1021491 0100001 10/17/2016 10/26/2016 10/21/2016 800099 80 $ - $ - 1021491 0100001 10/17/2016 10/26/2016 10/21/2016 800000 1 $ - $ - 1021490 0100001 10/17/2016 10/21/2016 10/20/2016 800099 5 $ - $ - 1021489 0600004 10/17/2016 11/14/2016 11/08/2016 901447 32 $ 57.04 $ 1,825.28 1021488 1200005 10/17/2016 11/14/2016 Not Shipped 907855 5 $ 425.00 $ 2,125.00 1021487 1800108 10/17/2016 11/14/2016 Not Shipped 903001 1 $ 422.96 $ 422.96 1021486 1800033 10/17/2016 11/14/2016 11/08/2016 900852 4 $ 73.11 $ 292.44 1021485 0400007 10/17/2016 11/14/2016 11/08/2016 907179 6 $ 139.95 $ 839.70 1021485 0400007 10/17/2016 11/14/2016 11/08/2016 904991 12 $ 42.28 $ 507.36 1021485 0400007 10/17/2016 11/14/2016 11/08/2016 905052 30 $ 52.07 $ 1,562.10 1021484 0600003 10/17/2016 11/14/2016 11/08/2016 902536 12 $ 62.79 $ 753.48 1021483 1300018 10/17/2016 11/14/2016 11/01/2016 903821 6 $ 125.70 $ 754.20 1021482 0700001 10/17/2016 11/10/2016 11/03/2016 900914 8 $ 74.32 $ 594.56 1021481 0400007 10/17/2016 11/10/2016 11/04/2016 906998 12 $ 67.99 $ 815.88 1021480 1300034 10/17/2016 11/16/2016 Not Shipped 907505 8 $ 130.07 $ 1,040.56 1021479 1300087 10/17/2016 12/21/2016 Not Shipped 906925 36 $ 45.00 $ 1,620.00 1021478 0100183 10/17/2016 11/22/2016 Not Shipped 904560 35 $ 87.19 $ 3,051.65 1021477 1800032 10/17/2016 12/23/2016 Not Shipped 906423 90 $ 60.00 $ 5,400.00 1021476 0900011 10/17/2016 11/09/2016 10/19/2016 904437 15 $ 3.95 $ 59.25 1021475 1300221 10/17/2016 10/20/2016 10/18/2016 900672 1 $ 117.36 $ 117.36 1021474 0100077 10/17/2016 11/10/2016 10/24/2016 902493 2 $ 61.88 $ 123.76 1021473 1300220 10/13/2016 11/17/2016 10/26/2016 906378 492 $ 106.28 $ 52,289.76 1021472 1300219 10/12/2016 11/16/2016 Not Shipped 902654 4 $ 144.54 $ 578.16 1021471 0400007 10/12/2016 11/09/2016 11/04/2016 906998 12 $ 67.99 $ 815.88 1021470 0300040 10/12/2016 11/09/2016 11/02/2016 906860 3 $ 159.50 $ 478.50 1021469 0100077 10/12/2016 11/08/2016 Not Shipped 905417 2 $ 50.00 $ 100.00 1021468 0400007 10/12/2016 11/10/2016 11/04/2016 907080 12 $ 73.92 $ 887.04 1021467 0400037 10/12/2016 11/09/2016 10/26/2016 907298 10 $ 55.00 $ 550.00 1021466 0300060 10/11/2016 11/07/2016 11/03/2016 902897 3 $ 232.40 $ 697.20 1021465 0400007 10/11/2016 11/07/2016 10/27/2016 907147 18 $ 50.00 $ 900.00 1021464 2200053 10/11/2016 11/14/2016 11/08/2016 906943 6 $ 187.02 $ 1,122.12 1021463 2000009 10/11/2016 11/08/2016 Not Shipped 903537 112 $ 39.52 $ 4,426.24 1021462 0400050 10/11/2016 11/07/2016 10/24/2016 907152 5 $ 60.92 $ 304.60 1021461 0300014 10/11/2016 11/04/2016 11/02/2016 901319 10 $ 36.87 $ 368.70 1021460 1200029 10/11/2016 11/07/2016 11/01/2016 900402 4 $ 289.73 $ 1,158.92 1021459 0600004 10/11/2016 11/04/2016 11/01/2016 907833 6 $ 173.05 $ 1,038.30 1021458 0200002 10/11/2016 11/09/2016 10/26/2016 902906 2 $ 53.09 $ 106.18 1021457 0200002 10/11/2016 11/18/2016 Not Shipped 902905 50 $ 34.87 $ 1,743.50 1021456 0400037 10/07/2016 11/04/2016 10/26/2016 902760 10 $ 60.00 $ 600.00 1021455 0400007 10/07/2016 11/03/2016 11/01/2016 906998 6 $ 67.99 $ 407.94 1021455 0400007 10/07/2016 11/03/2016 11/01/2016 906019 42 $ 54.24 $ 2,278.08 1021455 0400007 10/07/2016 11/03/2016 11/01/2016 907179 6 $ 139.95 $ 839.70 1021455 0400007 10/07/2016 11/03/2016 11/01/2016 906997 6 $ 64.00 $ 384.00 1021454 0100183 10/07/2016 01/20/2017 Not Shipped 906900 680 $ 79.55 $ 54,094.00 1021453 0100020 10/07/2016 11/02/2016 10/18/2016 907310 46 $ 84.36 $ 3,880.56 1021452 2000009 10/07/2016 11/01/2016 10/28/2016 904617 54 $ 45.44 $ 2,453.76 1021451 0100077 10/07/2016 11/01/2016 10/27/2016 901141 6 $ 71.01 $ 426.06 1021450 1000032 10/07/2016 10/14/2016 10/11/2016 900672 1 $ 117.36 $ 117.36 1021449 2000009 10/07/2016 11/03/2016 11/01/2016 905247 13 $ 157.94 $ 2,053.22 1021448 1800116 10/07/2016 10/18/2016 10/10/2016 900956 1 $ 230.62 $ 230.62 1021448 1800116 10/07/2016 10/18/2016 10/10/2016 900684 2 $ 13.74 $ 27.48 1021447 1900011 10/04/2016 10/31/2016 10/24/2016 900914 8 $ 79.82 $ 638.56 1021446 2000009 10/04/2016 11/01/2016 10/21/2016 903538 224 $ 34.66 $ 7,763.84 1021445 0400007 10/04/2016 10/31/2016 10/18/2016 907179 6 $ 139.95 $ 839.70 1021445 0400007 10/04/2016 10/31/2016 10/18/2016 905052 24 $ 52.07 $ 1,249.68 1021444 0400007 10/04/2016 11/01/2016 10/27/2016 907148 25 $ 45.00 $ 1,125.00 1021444 0400007 10/04/2016 11/01/2016 10/27/2016 907147 24 $ 50.00 $ 1,200.00 1021443 0100001 10/03/2016 10/21/2016 10/11/2016 802765 1 $ - $ - 1021442 0100001 10/03/2016 10/21/2016 10/11/2016 802765 4 $ - $ - 1021441 1300018 10/03/2016 11/28/2016 Not Shipped 903820 11 $ 103.17 $ 1,134.87 1021440 0400007 10/03/2016 10/26/2016 10/18/2016 907080 6 $ 73.92 $ 443.52 1021439 0900011 10/03/2016 10/28/2016 10/19/2016 900655 6 $ 72.60 $ 435.60 1021438 2000009 10/03/2016 10/25/2016 10/24/2016 903537 112 $ 39.52 $ 4,426.24 1021437 1300218 10/03/2016 10/28/2016 10/11/2016 902180 2 $ 50.88 $ 101.76 1021436 0100121 10/03/2016 10/28/2016 10/24/2016 902493 4 $ 58.33 $ 233.32 1021435 1800007 10/03/2016 10/28/2016 10/24/2016 904583 12 $ 63.00 $ 756.00 1021434 1300044 10/03/2016 10/27/2016 10/11/2016 900199 2 $ 227.36 $ 454.72 1021433 1300044 10/03/2016 10/27/2016 10/18/2016 901149 2 $ 249.31 $ 498.62 1021432 1300041 09/29/2016 10/27/2016 10/11/2016 903374 6 $ 136.51 $ 819.06 1021431 0400050 09/29/2016 10/24/2016 10/04/2016 907151 5 $ 54.76 $ 273.80 1021430 0400007 09/29/2016 10/25/2016 10/21/2016 907179 10 $ 139.95 $ 1,399.50 1021429 0400007 09/29/2016 10/25/2016 10/21/2016 907180 30 $ 146.30 $ 4,389.00 1021428 0400007 09/29/2016 10/24/2016 10/18/2016 906019 6 $ 57.07 $ 342.42 1021428 0400007 09/29/2016 10/24/2016 10/18/2016 907180 6 $ 146.30 $ 877.80 1021428 0400007 09/29/2016 10/24/2016 10/18/2016 906998 6 $ 67.99 $ 407.94 1021428 0400007 09/29/2016 10/24/2016 10/18/2016 904991 12 $ 42.28 $ 507.36 1021427 0600030 09/29/2016 10/24/2016 10/11/2016 900504 1 $ 237.17 $ 237.17 1021426 2000009 09/29/2016 10/26/2016 10/24/2016 903622 50 $ 18.50 $ 925.00 1021425 0300040 09/28/2016 10/25/2016 10/10/2016 906860 2 $ 159.50 $ 319.00 1021424 0200031 09/28/2016 10/27/2016 10/17/2016 901447 26 $ 59.33 $ 1,542.58 1021423 0600004 09/28/2016 10/25/2016 10/18/2016 900913 58 $ 53.82 $ 3,121.56 1021422 1800032 09/28/2016 12/12/2016 Not Shipped 906423 65 $ 60.00 $ 3,900.00 1021421 2000009 09/26/2016 11/01/2016 10/28/2016 903626 24 $ 116.00 $ 2,784.00 1021420 2000041 09/26/2016 01/24/2017 Not Shipped 901027 474 $ 38.00 $ 18,012.00 1021419 1800007 09/26/2016 11/11/2016 10/13/2016 904111 6 $ 73.70 $ 442.20 1021418 1600005 09/26/2016 10/20/2016 10/10/2016 903640 3 $ 67.70 $ 203.10 1021417 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021416 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021415 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021414 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021413 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021412 1300117 09/26/2016 03/31/2017 Not Shipped 907380 150 $ 17.08 $ 2,562.00 1021411 1300117 09/26/2016 10/28/2016 10/27/2016 907380 150 $ 17.08 $ 2,562.00 1021410 0100020 09/23/2016 10/21/2016 10/18/2016 906751 24 $ 113.44 $ 2,722.56 1021410 0100020 09/23/2016 10/21/2016 10/18/2016 905279 4 $ 79.27 $ 317.08 1021409 2200045 09/23/2016 10/21/2016 10/18/2016 907939 2 $ 152.56 $ 305.12 1021408 0400007 09/22/2016 10/20/2016 10/18/2016 907080 6 $ 73.92 $ 443.52 1021408 0400007 09/22/2016 10/20/2016 10/18/2016 907180 12 $ 146.30 $ 1,755.60 1021408 0400007 09/22/2016 10/20/2016 10/18/2016 907179 6 $ 139.95 $ 839.70 1021408 0400007 09/22/2016 10/20/2016 10/18/2016 904991 30 $ 42.28 $ 1,268.40 1021408 0400007 09/22/2016 10/20/2016 10/18/2016 905052 18 $ 52.07 $ 937.26 1021407 0600004 09/22/2016 10/21/2016 10/17/2016 901447 26 $ 57.04 $ 1,483.04 1021406 0300076 09/22/2016 10/20/2016 09/27/2016 903388 636 $ 1.37 $ 871.32 1021405 0700003 09/21/2016 02/03/2017 Not Shipped 900306 15 $ 92.17 $ 1,382.55 1021404 2000036 09/21/2016 10/19/2016 10/10/2016 900944 8 $ 97.44 $ 779.52 1021403 1800007 09/21/2016 10/17/2016 10/05/2016 904443 2 $ 191.06 $ 382.12 1021402 1800007 09/21/2016 10/21/2016 10/13/2016 904443 4 $ 191.06 $ 764.24 1021401 1800007 09/21/2016 10/17/2016 10/05/2016 904112 24 $ 71.25 $ 1,710.00 1021400 0300040 09/21/2016 10/19/2016 10/10/2016 906769 2 $ 317.87 $ 635.74 1021399 0400007 09/21/2016 10/19/2016 10/10/2016 904991 12 $ 42.28 $ 507.36 1021398 0600030 09/21/2016 10/19/2016 10/03/2016 901041 2 $ 51.63 $ 103.26 1021397 1700045 09/19/2016 10/14/2016 10/10/2016 906497 3 $ 53.36 $ 160.08 1021396 0500036 09/19/2016 10/14/2016 10/03/2016 903664 24 $ 110.90 $ 2,661.60 1021395 0700070 09/19/2016 10/06/2016 10/03/2016 900914 2 $ 82.73 $ 165.46 1021394 0400007 09/19/2016 10/13/2016 10/10/2016 907179 6 $ 139.95 $ 839.70 1021394 0400007 09/19/2016 10/13/2016 10/10/2016 906019 6 $ 57.07 $ 342.42 1021394 0400007 09/19/2016 10/13/2016 10/10/2016 906998 6 $ 67.99 $ 407.94 1021394 0400007 09/19/2016 10/13/2016 10/10/2016 904991 12 $ 42.28 $ 507.36 1021393 0300021 09/16/2016 10/12/2016 10/04/2016 901446 24 $ 54.98 $ 1,319.52 1021392 0900011 09/16/2016 10/13/2016 10/03/2016 900656 1 $ 70.18 $ 70.18 1021391 1800015 09/16/2016 10/13/2016 10/04/2016 900852 20 $ 61.29 $ 1,225.80 1021390 2000009 09/16/2016 10/14/2016 09/27/2016 903920 4 $ 32.17 $ 128.68 1021389 1400046 09/16/2016 10/15/2016 10/04/2016 900954 16 $ 118.87 $ 1,901.92 1021388 0700003 09/16/2016 11/10/2016 Not Shipped 900300 20 $ 87.99 $ 1,759.80 1021387 0600004 09/14/2016 10/04/2016 09/22/2016 901447 2 $ 57.04 $ 114.08 1021386 0100020 09/14/2016 10/12/2016 09/22/2016 906445 8 $ 77.38 $ 619.04 1021385 1800007 09/14/2016 10/21/2016 10/13/2016 904110 24 $ 83.06 $ 1,993.44 1021384 2200001 09/14/2016 10/12/2016 10/04/2016 901446 4 $ 57.19 $ 228.76 1021383 0400007 09/14/2016 10/12/2016 10/03/2016 907148 6 $ 33.00 $ 198.00 1021382 1800108 09/14/2016 10/12/2016 09/27/2016 903001 1 $ 422.96 $ 422.96 <tbody> </tbody>

<tbody>
</tbody>

 

[AhoyNC]

To count the unique orders try one of these array formulas.
The formulas must be entered with CTRL-SHIFT-ENTER (command-return on MAC).
Based on the data you posted.
If the So_Nbr are actual numbers (numeric field) then try:

=SUM(IF(FREQUENCY(IF($E$2:$E$223>=$M$2,IF($E$2:$E$223<=$N$2,$A$2:$A$223)),$A$2:$A$223),1))

If they are text then try:

=SUM(IF(FREQUENCY(IF($E$2:$E$223>=$M$2,IF($E$2:$E$223<=$N$2,MATCH($A$2:$A$223,$A$2:$A$223,0))),ROW($A$2:$A$223)-ROW($A$2)+1),1))

Row\Col​	A​	B​	C​	D​	E​	F​	L​	M​	N​
1​	So_Nbr​	Cust_Nbr​	Name​	Cust_Po_Nbr​	Taken_Date​	ReqdDate​		Start Date​	End Date​
2​	1021576​	200079​			42683​	42710​		42658​	42689​
3​	1021574​	1100048​			42683​	42710​
4​	1021575​	700020​			42683​	42724​		Unique​
5​	1021573​	2000009​			42683​	42712​		Count​
6​	1021572​	900060​			42683​	42710​		102​
7​	1021571​	300021​			42683​	42710​
8​	1021570​	1400046​			42682​	42710​
9​	1021569​	2200001​			42682​	42710​
10​	1021568​	1900011​			42682​	42710​

<tbody>
</tbody>

 

[Davereau]

I got it to work with your formulas AhoyNC! Thank you very much. I have been learning so much from these forums.

Below is the table I use those formulas in. The only thing I would like to do now is to exclude a certain type of product from my totals. In my master table, Column J lists the product family code for the corresponding product. The product I would like to exclude from these totals is the family of "SAMP" and would be listed in that column. What would be required in the existing formulas in order to exclude the SAMP product family?

# of Orders formula, =SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,Data!$A:$A)),Data!$A:$A),1))
# of Customers formula, =SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,Data!$B:$B)),Data!$B:$B),1))
# of Diff Parts formula, =SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,Data!$I:$I)),Data!$I:$I),1))

These were taken from the January column with D3 being 01/01/2016 and D4 being 01/31/2016. Just trying to figure out how to exclude the row from the totals that would have "SAMP" in column J.

2016 Entered Orders
		January	February	March	April	May	June
YTD		01/01/2016	02/01/2016	03/01/2016	04/01/2016	05/01/2016	06/01/2016
		01/31/2016	02/29/2016	03/31/2016	04/30/2016	05/31/2016	06/30/2016
779	# of Orders	83	87	102	92	95	82
192	# of Customers	44	45	43	46	46	46
377	# of Diff Parts	77	72	85	82	91	81
33,386	# of Total Parts	1524	3476	2877	2136	3567	2463
1,739,527.88	Total $ Value	90,767.78	223,590.35	210,218.06	88,910.81	177,494.03	139,497.63
52%	Avg Margin	52%	53%	56%	52%	46%	44%
		July	August	September	October	November	December
		07/01/2016	08/01/2016	09/01/2016	10/01/2016	11/01/2016	12/01/2016
Average		07/31/2016	08/31/2016	09/30/2016	10/31/2016	11/30/2016	12/31/2016
92	# of Orders	66	95	107	104	43	0
45	# of Customers	35	51	44	54	25	0
80	# of Diff Parts	67	94	76	87	37	0
3139	# of Total Parts	1919	2659	6372	4152	2241	0
158,138.90	Total $ Value	111,585.19	148,945.42	201,748.82	262,130.32	84,639.47	0
50%	Avg Margin	46%	49%	54%	49%	52%	#DIV/0!

<tbody>
</tbody>

 

[AhoyNC]

Try the following. This are all array formulas and must be entered with CTRL-SHIFT-ENTER.
When using array formulas it is best to not reference whole columns as this can slow calculations down. Assuming row 1 had headers and your data won't go below say row 5000 then you could reference a range like A2:A7000 or turn your data range into a table that will expand as you add new data.

# of Orders formula

=SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,IF($J:$J<>"SAMP",Data!$A:$A))),Data!$A:$A),1))

# of Customers formula

=SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,IF($J:$J<>"SAMP",Data!$B:$B))),Data!$B:$B),1))

# of Diff Parts formula

=SUM(IF(FREQUENCY(IF(Data!$E:$E>=D3,IF(Data!$E:$E<=D4,IF($J:$J<>"SAMP",Data!$I:$I))),Data!$I:$I),1))

 

[Davereau]

I tried something similar but didn't work, but I think you have better placement of the quotation marks . Will let you know how it works.

As far as the whole column referencing, i haven't had any problems but might make that change anyways.

 

[AhoyNC]

When using an array formula it is going to run on all cells that you reference even if they are empty. So in later ver. of Excel the formula is going to look at all one million plus rows. Since you have 3 array formulas it going to do this 3 times which can start to slow things down. So, if you do start to have problems in the future just keep that in mind.

 

[Davereau]

I got everything to work fine. The only thing I have to do that I didn't anticipate is to go to each of the columns on the data tab that I reference in my formulas and perform a "text to columns" to revert the cells to a general format. It is the only way I have found to have the results returned. The file is has to be updated each day as it is tied to our MRP database through a query. Each time I update, I need to revert to general format. It isn't that big of a deal as I don't use this file everyday.

Thanks again for all your help. It is nice to be able have all the data I require in one file than having to open multiple files all the time.

Dave

 

[AhoyNC]

You're welcome. Thanks for the feedback.
If you have an IT dept. you may want to check if there is a way to bring the data over in a different format that would save you from the "text to column". I use to have to deal with both MRP and ERP systems that would bring dates in as text.

 

[Davereau]

We don't have IT onsite, we contract a firm. If there were any onsite, I guess that would be me and well, that isn't saying much . The information is stored in a Access Database and the only format I know of is to use excel.