Sum up quantity based on booking reference and dates of different weeks/months

[Anuru]

Hi Experts,

The attached work sheet summarizes information from "data sheet" to the "summary sheet". Text data are summarizes as it is, however, the "order qty" should be summarized based on "booking reference" and the "ex factory date". The formula for this qty calculation should consider "Booking reference" and also "ex factory dates". For example, for a particular booking reference, if TWO qtys appear under the SAME EX FACTORY date, those two qty should be added together and show up on the summary sheet, and also, if the same booking reference has another qty under a different ex factory date, it should also show up on the summary sheet separately.

The given below is the actual scenario;

The style swrmg1295 has 9 delivery splits (as shown in "data sheet")

My requirement is to get the SUM of the quantities based on BOOKING REFERENCE and also EX FACTORY DATE.

For example, for Booking reference swrmg1295, the 12th May qty that should appear on 'summary sheet' should be 19992.
However, the summary sheet shows 119952, which is the total qty of the style (which has spread in different ex factory dates)

Ideally this should appear on the summary sheet as follows;

12 May - 19992
19 May - 19992
26 May - 19872
2 Jun - 10008
23 Jun - 20064 (this has two quantities on the same date), 10032+10032)
30 Jun - 10008
7 July - 10032
21 July -9984

In other words, the same booking reference can appear as many as different ex factory dates exists. However,in case two or three or more quantities exist under the SAME EX FACTORY date, those should be summed up into ONE entry (row)

Pls help to get this solved...

[JeteMc]

Try the following formula:

Please Login or Register  to view this content.

Let us know if you have any questions.