# 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

#### Help101

##### New Member
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

<tbody>
</tbody>

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

<tbody>
</tbody>

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### RasGhul

##### Well-known Member
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;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">01-06-19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">02-06-19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Jun-03</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4472C4;;">Jun-22</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;color: #FFFFFF;background-color: #ED7D31;;">05-06-19</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">17-06-19</td><td style="text-align: center;color: #FFFFFF;background-color: #4472C4;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Preferred</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=C1+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">\$C\$1:\$X\$1>=\$Z\$1</font>)*(<font color="Red">\$C\$1:\$X\$1<=\$AA\$1</font>)*(<font color="Red">\$C\$2:\$X\$2=\$B3</font>)*(<font color="Red">\$C3:\$X3>=\$AB\$1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### Help101

##### New Member
=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

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

#### RasGhul

##### Well-known Member
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;">1/06/2019</td><td style="text-align: right;;">22/06/2019</td><td style="text-align: right;;"></td><td style="color: #FFFFFF;background-color: #4472C4;;">Start</td><td style="color: #FFFFFF;background-color: #4472C4;;">End</td><td style="color: #FFFFFF;background-color: #4472C4;;">Start</td><td style="color: #FFFFFF;background-color: #4472C4;;">End</td><td style="text-align: center;color: #FFFFFF;background-color: #ED7D31;;">Qty 1</td><td style="text-align: center;color: #FFFFFF;background-color: #ED7D31;;">Qty 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Customer</td><td style="text-align: center;;">Preferred</td><td style="text-align: right;;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">5/06/2019</td><td style="text-align: right;;">17/06/2019</td><td style="text-align: right;;">19/06/2019</td><td style="text-align: right;;">22/06/2019</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Customer 1</td><td style="text-align: center;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #FFFFFF;;"></td><td style="text-align: center;color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Customer 2</td><td style="text-align: center;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Customer 3</td><td style="text-align: center;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Customer 4</td><td style="text-align: center;;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Customer 5</td><td style="text-align: center;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Customer 6</td><td style="text-align: center;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Customer 7</td><td style="text-align: center;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Customer 8</td><td style="text-align: center;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Customer 9</td><td style="text-align: center;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Customer 10</td><td style="text-align: center;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">\$C\$1:\$X\$1>=\$Z\$2</font>)*(<font color="Red">\$C\$1:\$X\$1<=\$AA\$2</font>)*(<font color="Red">\$C\$2:\$X\$2=\$B3</font>)*(<font color="Red">\$C3:\$X3=\$AD\$2</font>)</font>)+SUMPRODUCT(<font color="Blue">(<font color="Red">\$C\$1:\$X\$1>=\$Z\$2</font>)*(<font color="Red">\$C\$1:\$X\$1<=\$AA\$2</font>)*(<font color="Red">\$C\$2:\$X\$2=\$B3</font>)*(<font color="Red">\$C3:\$X3=\$AE\$2</font>)</font>)+SUMPRODUCT(<font color="Blue">(<font color="Red">\$C\$1:\$X\$1>=\$AB\$2</font>)*(<font color="Red">\$C\$1:\$X\$1<=\$AC\$2</font>)*(<font color="Red">\$C\$2:\$X\$2=\$B3</font>)*(<font color="Red">\$C3:\$X3=\$AD\$2</font>)</font>)+SUMPRODUCT(<font color="Blue">(<font color="Red">\$C\$1:\$X\$1>=\$AB\$2</font>)*(<font color="Red">\$C\$1:\$X\$1<=\$AC\$2</font>)*(<font color="Red">\$C\$2:\$X\$2=\$B3</font>)*(<font color="Red">\$C3:\$X3=\$AE\$2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### Help101

##### New Member

ADVERTISEMENT

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

#### Help101

##### New Member
Thanks very much RasGhul. Your formula worked!

Regards,

Help101

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

#### RasGhul

##### Well-known Member
You're welcome thanks for feedback

Replies
4
Views
95
Replies
3
Views
243
Replies
7
Views
116
Replies
1
Views
51
Replies
6
Views
102

Threads
1,141,406
Messages
5,706,268
Members
421,437
Latest member
GijoeBlack

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

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