Formula to workout equipment required

Hi All,

So I am trying to work out how to get the total number of laptops, printers scanners that are required on each day of the year and display it on the Equipment Weekly tab. I have managed to work this out for Equipment Monthly Tab.
I have a planner for each year which shows peoples movements and what kit is required. My wish is to pull this information onto the Equipment Weekly tab, in such away as that I can change the year the Equipment Weekly form is looking at.
The equipment quantity needs to be reflected for the number of days it is required for the project and then added to any other projects that have equipment required for the same day to give a total requirement across all projects for the day.

I just can’t get my head around how to work this out. i need the equipment quantities x Project Duration Day (s) but it needs to be shown cumulatively for any all projects that have equipment needs on those dates, some how.

I also seem to have some performance issues I think this maybe down o the formulas used on the stats page.

Any suggestion on top on how to improve this would be greatly welcomed.

Originally Posted by 6StringJazzer

I am unclear as to how your 2021 sheet works. I suspect that your design is working against you. Walk us through what is going on there.

So From Left to right


SP Sales Persons initials, a drop down box to see
Status, Shows current status of the projects Won, Lost, Pending Start, Active etc
Customer, customer name
Project, Project name
Location where the project is
Event Type, Onsite full, Onsite- part, delivery etc. shows us if people are needed on site etc
From Date & start Date, are when the projects are and finish. These dates fill out the planner section to the right hand side to give you an overview.
Event Duration This number of days of the project.
PM Project manager initials
Number of Onsite Staff required. How many people needed onsite
OM 1 & OM 2 are both initials of the onsite managers sometimes more than 1 is required. This is colour coded and fills in the planner date section in and fills the initials in the relevant boxes under the date so we can see an overview of the projects.
Vehicle is the assigned vehicle type
Despatch date if despatch is different for any reason this is listed. Highlights on the planner section as a red square.
Number of stations This is basically what we take to site and is the finished product total quantities as a unit. This get broken down further over to is component parts
Online reg is a yes no dropdown.

Badge Type column R through to AG are all products with dropdowns that pull from the info page to make it dynamic.
AH through to AO are all equipment totals that we take to site to build the product when we arrive.
This is where I need the help to pull the info through onto the Equipment weekly sheet.
So I would like the 5 stations that are being used for 2 days in the first example to show on the Equipment sheet a 5 for the dates on the 4/1 and 5/1 as for those 5 stations are used on two days so will be unavailable for use anywhere else.
The second line then with the 10 is put against the relevant dates, but if they have equipment out on the same day those days would reflect the totals(20)
Hope this helps a little the planner pulls all the names and colours from the om 1 & 2 columns