Rolling totals calculated based on two maybe 3 variables

Not sure if the thread title fits the concern but here is what I have.

Using Excel 2010


I need some help with rolling totals using two type of variables in the data. I looked around and tried several types of solutions I found but can't seem to get them to work as expected.

I attached a sheet with examples that mimic the original data (Columns A to H) and the types of values that I am looking to capture in columns K through M.

On The Breakdown Summary as an example:
I need to know the count on Order Totals Per Customer By Category Type. Would I use some type of marriage of the Frequency and SumProduct functions or would there be another method to get rolling totals. Figured I could use the solution on this to work with the other fields.

On Order Totals Per Customer By Category Type, one part of the calculation request is that if a Product Category appears more than once on an order only count it as one and only count the total dollar one time. I highlighted an example in green on order 4523633 the product category FT appears twice and thus the request is to only total the dollar amount once when that transaction occurs. The order was double ordered, then cancelled and should only be counted once in the totals at $68.50. Hope that makes sense.

Figured if I could get the above calculations worked out I could work the two following sections in yellow for type and category.

I see this request coming my way soon too so figure I would through it in. On all the calculations for the above may need to omit the Status S or O when needed from the rolling totals.

I only provided 20 records I actually have tens of thousands that were sent my way to work with.

Hope I explained this properly. Thank you in advance for any feedback or help you can provide.

order-countsum-test2.xlsx

I added some total examples for reference and also updated a few other things as I overlooked them on the prior sheet. Thanks so much for looking at it.

Originally Posted by dilipandey

Hi Dotsofcolor,

Would suggest you to put the number manually so that I can relate with your explanation and would try to achieve the same using formulas. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>