MAX amount in 30 days with multiple criteria.

[Arturs_I]

Greetings everyone.

I would like to ask some help in creating a formula, that I`ve been trying to create myself for a long time.

My aim is to create a formula, where I can see for each item, the MAX amount used in 2018, based on the rolling SUM.

I have attached a file, where I have created a formula and a Pivot table, for gathering the necessary data, where you can look at my progress. As I have 1195 Items and in the future, I will have more, I takes a lot of time in gathering necessary data for each Item.

I hope I have explained everything properly.

[Bo_Ry]

Please try at Max I2
but this is very slow.

Please Login or Register  to view this content.

or try with helper column in Data D2
=SUMIFS($C$2:$C$8662,$B$2:$B$8662,B2,$A$2:$A$8662,">="&A2,$A$2:$A$8662,"<="&A2+30)

and Max J2
=MAX(INDEX(Data!$D$2:$D$8662*(Data!$B$2:$B$8662=H4),))

[Arturs_I]

Thank you very much!!! You made my day Everything works as I wanted
I must say, that It will take me a while to figure out how did you do that, but that`s another chalange!

May next year be filled with success and bring you many wonderful things - Happy new year!

Thanks again,
Arturs

[Bo_Ry]

Happy to help and Happy new year