Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame
Results 1 to 7 of 7

Thread: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    Hi Everyone,

    We have a daily log of the number of parcels picked up by our customers. The number of parcels to be picked up ranges from 0 to a maximum of 5 per day.

    Customers are each assigned a pick-up day (Column C) if they are picking up 4 or more parcels. Row 1 shows the date the parcels are picked up and Row 2 shows the week day corresponding to the pick-up dates.

    Our challenge is that customers do not always follow their pick-up dates for 4 or more parcels. We would like to track (Column Z) and reward our customers who complied with their pick-up dates for 4 or more parcels from June 14th to June 16th (Column G to Column S).

    We have more than 10 customers so counting manually is very challenging. I have reviewed online tutorials on this but it seems that the COUNTIFS function only works if the variables tracked are found in columns. Our records are in rows.

    Thanks for the help.

    Help101

    June 1 June 2 June 3 June 4 June 5 June 6 June 7 June 8 June 9 June 10 June 11 June 12 June 13 June 14 June 15 June 16 June 17 June 18 June 19 June 20 June 21 June 22
    Customer Preferred Pick-up Day 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 Number of Time Customer Picked Up 4 or more Parcels on Preferred Pick-up Day
    1 Customer 1 3 5 2 0 4 2 3 0 2 5 2 4 2 1 0 0 1 1 2 3 4 0 2
    2 Customer 2 5 2 1 2 4 0 5 1 4 3 1 0 2 1 2 5 4 3 4 4 0 3 3
    3 Customer 3 2 1 1 0 5 0 3 2 3 3 0 1 4 0 1 2 4 4 1 5 4 0 1
    4 Customer 4 7 0 3 2 4 5 0 3 2 0 5 5 4 0 0 1 5 0 1 2 5 5 0
    5 Customer 5 2 2 1 1 2 0 5 4 4 1 3 4 5 4 2 4 5 0 2 1 4 2 0
    6 Customer 6 1 3 3 1 0 0 4 4 1 1 5 3 4 1 4 1 0 1 0 1 1 4 5
    7 Customer 7 3 1 1 2 5 3 0 3 1 2 5 3 4 3 3 0 2 4 1 4 5 0 5
    8 Customer 8 6 1 1 4 3 0 4 0 0 4 3 4 4 4 3 0 1 5 0 3 2 4 3
    9 Customer 9 2 2 2 2 1 0 5 0 2 2 1 1 2 4 4 4 3 1 2 4 0 2 0
    10 Customer 10 5 1 1 0 4 4 3 5 1 4 0 2 5 3 3 3 1 1 5 1 1 0 5

    ****** id="cke_pastebin" style="position: absolute; top: 660px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
    1 0 5 0 3 2 3 3

  2. #2
    Board Regular
    Join Date
    Jul 2016
    Posts
    496
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    Hi Help,

    Pls disregard post 2#

    Hi,

    You'll need to use the date formula to give you real June dates from 01-06-19, then you can format them as mmm-dd if you want Month / Day like your sample.

    (Column G to Column S) Should mean 05-06-19 to 17-06-19 which is ok because you can change the Start & End date or the Parcel starting qty with the following;

    ABCDEXYZAAAB
    101-06-1902-06-19Jun-03Jun-2205-06-1917-06-194
    2CustomerPreferred7127
    3Customer 1352021
    4Customer 2521231
    5Customer 3211011
    6Customer 4703200
    7Customer 5221100
    8Customer 6133150
    9Customer 7311250
    10Customer 8611430
    11Customer 9222200
    12Customer 10511050

    Sheet1



    Worksheet Formulas
    CellFormula
    D1=C1+1
    Z3=SUMPRODUCT(($C$1:$X$1>=$Z$1)*($C$1:$X$1<=$AA$1)*($C$2:$X$2=$B3)*($C3:$X3>=$AB$1))


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    =SUMPRODUCT(($C$1:$X$1>=$Z$1)*($C$1:$X$1<=$AA$1)*($C$2:$X$2=$B3)*($C3:$X3>=$AB$1))

    Hi RasGhul,

    Thanks very much. The formula above that you gave me works! On our spreadsheet, the dates are formatted properly so there were no issues.

    Further questions would be the following.

    1) How will the formula change if we add another lookup period. For example, instead of June 5-17 only, we also want info for both June 5-17 and June 19-22?
    2) The above formula gives us the count for parcel pick-ups for 4-5 parcels. How will the formula change if we want info for pickups done on preferred dates for both 1 parcel and 3 parcels (added together)?

    Thanks again.

    Help101



    Quote Originally Posted by RasGhul View Post
    Hi Help,

    Pls disregard post 2#

    Hi,

    You'll need to use the date formula to give you real June dates from 01-06-19, then you can format them as mmm-dd if you want Month / Day like your sample.

    (Column G to Column S) Should mean 05-06-19 to 17-06-19 which is ok because you can change the Start & End date or the Parcel starting qty with the following;

    A B C D E X Y Z AA AB
    1 01-06-19 02-06-19 Jun-03 Jun-22 05-06-19 17-06-19 4
    2 Customer Preferred 7 1 2 7
    3 Customer 1 3 5 2 0 2 1
    4 Customer 2 5 2 1 2 3 1
    5 Customer 3 2 1 1 0 1 1
    6 Customer 4 7 0 3 2 0 0
    7 Customer 5 2 2 1 1 0 0
    8 Customer 6 1 3 3 1 5 0
    9 Customer 7 3 1 1 2 5 0
    10 Customer 8 6 1 1 4 3 0
    11 Customer 9 2 2 2 2 0 0
    12 Customer 10 5 1 1 0 5 0
    Sheet1

    Worksheet Formulas
    Cell Formula
    D1 =C1+1
    Z3 =SUMPRODUCT(($C$1:$X$1>=$Z$1)*($C$1:$X$1<=$AA$1)*($C$2:$X$2=$B3)*($C3:$X3>=$AB$1))

  4. #4
    Board Regular
    Join Date
    Jul 2016
    Posts
    496
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    Ok the following, if I understand correctly;

    Date range 5/06 - 17/06, on Preferred Day and Qty 1 & Qty 3 then
    Date range 19/06 - 22/06, on Preferred Day and Qty 1 & Qty 3 added together.

    ABCXYZAAABACADAE
    11/06/201922/06/2019StartEndStartEndQty 1Qty 2
    2CustomerPreferred775/06/201917/06/201919/06/201922/06/201913
    3Customer 13520
    4Customer 25231
    5Customer 32110
    6Customer 47001
    7Customer 52201
    8Customer 61351
    9Customer 73151
    10Customer 86131
    11Customer 92202
    12Customer 105153

    Sheet1



    Worksheet Formulas
    CellFormula
    Z3=SUMPRODUCT(($C$1:$X$1>=$Z$2)*($C$1:$X$1<=$AA$2)*($C$2:$X$2=$B3)*($C3:$X3=$AD$2))+SUMPRODUCT(($C$1:$X$1>=$Z$2)*($C$1:$X$1<=$AA$2)*($C$2:$X$2=$B3)*($C3:$X3=$AE$2))+SUMPRODUCT(($C$1:$X$1>=$AB$2)*($C$1:$X$1<=$AC$2)*($C$2:$X$2=$B3)*($C3:$X3=$AD$2))+SUMPRODUCT(($C$1:$X$1>=$AB$2)*($C$1:$X$1<=$AC$2)*($C$2:$X$2=$B3)*($C3:$X3=$AE$2))


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    Thanks RasGhul. I have not had the chance to use your new formula yet. I will test these on our data.

    I would also like to share with you the formula below that I tried by myself but based on your example. This formula counts pick-up for both 1 and 3 parcels for June 5-17, 2019. So far, it works.

    =SUMPRODUCT(($C$1:$X$1>=$Y$1)*($C$1:$X$1<=$Z$1)*($C$2:$X$2=$B3)*(($C3:$X3=$AA$1-3)+($C3:$X3=$AA$1-1))), where $AA$1=4 (from your older table). This formula is shorter.

    Instead of

    =SUMPRODUCT(($C$1:$X$1>=$Y$1)*($C$1:$X$1<=$Z$1)*($C$2:$X$2=$B3)*($C3:$X3=$AA$1-3) + =SUMPRODUCT(($C$1:$X$1>=$Y$1)*($C$1:$X$1<=$Z$1)*($C$2:$X$2=$B3)*($C3:$X3=$AA$1-1). This formula is longer.

    Regards,

    Help101

  6. #6
    New Member
    Join Date
    Apr 2014
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    Thanks very much RasGhul. Your formula worked!

    Regards,

    Help101

    Quote Originally Posted by RasGhul View Post
    Ok the following, if I understand correctly;

    Date range 5/06 - 17/06, on Preferred Day and Qty 1 & Qty 3 then
    Date range 19/06 - 22/06, on Preferred Day and Qty 1 & Qty 3 added together.

    A B C X Y Z AA AB AC AD AE
    1 1/06/2019 22/06/2019 Start End Start End Qty 1 Qty 2
    2 Customer Preferred 7 7 5/06/2019 17/06/2019 19/06/2019 22/06/2019 1 3
    3 Customer 1 3 5 2 0
    4 Customer 2 5 2 3 1
    5 Customer 3 2 1 1 0
    6 Customer 4 7 0 0 1
    7 Customer 5 2 2 0 1
    8 Customer 6 1 3 5 1
    9 Customer 7 3 1 5 1
    10 Customer 8 6 1 3 1
    11 Customer 9 2 2 0 2
    12 Customer 10 5 1 5 3
    Sheet1

    Worksheet Formulas
    Cell Formula
    Z3 =SUMPRODUCT(($C$1:$X$1>=$Z$2)*($C$1:$X$1<=$AA$2)*($C$2:$X$2=$B3)*($C3:$X3=$AD$2))+SUMPRODUCT(($C$1:$X$1>=$Z$2)*($C$1:$X$1<=$AA$2)*($C$2:$X$2=$B3)*($C3:$X3=$AE$2))+SUMPRODUCT(($C$1:$X$1>=$AB$2)*($C$1:$X$1<=$AC$2)*($C$2:$X$2=$B3)*($C3:$X3=$AD$2))+SUMPRODUCT(($C$1:$X$1>=$AB$2)*($C$1:$X$1<=$AC$2)*($C$2:$X$2=$B3)*($C3:$X3=$AE$2))

  7. #7
    Board Regular
    Join Date
    Jul 2016
    Posts
    496
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula needed to track the number of times each customer picked up 4 or more parcels on the preferred pick up day within a given time frame

    You're welcome thanks for feedback

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •