SUMIF based on multiple successive dates

[Ramsel]

Hi all,

Long-time lurker, first time poster for a problem I'm having trouble solving.

Background:
I have a data-set for a year's worth of productions for some 100 products. I've limited the attachment to 2 months.
The data set contains the date of production, week of production, product code, pcs/pack, type of booking and quantity.
Even though a production run can last for multiple days, system limitations generate a new production order for each day given a distorted overview of the number of production runs and the run size if I use this as a criteria for my sumif and countif formula. If I use the weeknumber as a criteria, I have problems with runs which happen during the transition from one week to the other.
As an added complexity, there are corrections on the production orders output which might happen days later.

Possible workaround:
If there is a product of which there is an output during successive dates, logic determines that this was 1 production run.

• How to code the criteria in the sumifs function to sum up the productions of the same product code of successive dates?
• How to avoid having duplicates using this way of working?
• How to integrate the required corrections on the production orders?

Another approach generating the same results is of course welcome as well, as is pointing me in the right direction with similar questions from the past. (Haven't been able to find them yet)
Hopefully the knowhow here limits the ever increasing bald spot on my head that was creating from scratching my head trying to resolve this issue.

Thanks in advance!

[XLent]

So, not sure if I fully understand but, if you can sort your data this would allow to (potentially) add a basic Boolean test, for use in subsequent SUMIFs or a Pivot (as a Page filter)

e.g. sort by Product Order, Type & Date -- then:

Please Login or Register  to view this content.

so, for PO19/001890 you would get Output of 118,800 (i.e. the 408 entry would be FALSE), and the Correction of -264

if the above is correct, but you want to achieve the same thing without the sort it will be a little more expensive, computationally, especially with big datasets.

alternatively you might consider using Power Query.

[XLent]

@Ramsel, apologies, my prior suggestion requires a correction, assuming I followed the required logic in the first instance of course !

Please Login or Register  to view this content.

[Ramsel]

Thanks Xlent.
Believe you might have pointed me to the right approach using a Boolean test.
Will have to sort by product code instead of production order to avoid problems as below where the same product code produced on the same day was logged under 2 different production orders.
In reality this will have been 1 production run.

Date	Week	Product code	Pcs/Carton	Production order	Type	Quantity
11/05/2019	19	605603	12	PO19/003153	Output	66.240
11/05/2019	19	605603	12	PO19/003154	Output	27.252

Will check if I can build on this approach, possibly using duplicate data sets sorted differently depending on the sumif-criteria I require.