Need Weekly Breakdown According to Date Rcvd Along with Amount of Orders and Dollars

[QuiteGoodNow]

Hi Everyone -

I'm trying completely revamp a spreadsheet to be more efficient than the one my predecessor left me with. I'm attaching the new spreadsheet (Drop Ship Report) and the old spreadsheet (Summary) for viewing.

The goal is to have data that is calculated from when the order was placed to when the report needs to be produced (usually every Friday). Within those two dates, I need to have excel compile a list of the item types that we offer (Driver Scooter, Mega Motion, Misc. E-Wheels) and within each category I need to show the number of orders along with the total dollars for each category (backordered, cancelled, completed, pending, replacement/exchange, and returned).

I've been trying to figure this out since November 2020 but I'm having no luck and I'm not sure if formulas would be the way to go or VBA. I've never done anything this complicated before.

On the DROP SHIP REPORT file, the working data is on the Drop Ship Report tab, which needs to be compiled onto the Weekly Breakdown tab. The Date Ordered is column G on the Drop Ship Report Tab and the reporting date is B2 on the Weekly Breakdown Tab.

I'm including the original spreadsheet (Summary spreadsheet using the Summary tab) to show how my predecessor manually calculated all of the data and how the data needs to appear with all of the calculation at the bottom. Please don't mind all of the colors on this spreadsheet as this is the only way he could figure out how to compile and differentiate the data.

Any help would be totally appreciated and if anyone has any questions, please let me know. Thank you so much to everyone looking at this post.