Formula to allocate sales orders based on working days

[Andylmarks]

I have 6 team members. Every Monday I run a report which shows how many sales orders were processed for the previous week. The report can't tell me who on my team processed the order so I want to proportion out total orders based on who was working each day of the week.

Attached is a sample file. Data in the yellow cells shows the report data I can pull showing orders processed by day.
I would like a formula in the orange calls (showing fabricated numbers) which takes the orders and proportions them to each member of the team based on if they were working that day, rather than having to figure it out manually, using a link to the holiday tracker shown on the other tab.

eg: On 8/11/20, Nick was on holiday, so orders need to be split across the remaining 5 team members. On 9/11/20, all team members working, so can be split across all 6. On 10/11/20, Kim and Gavin were off, so split that days orders across the remaining 4 team members etc. So I get a total per person for the week which comes back to total orders processed.

TIA!

[Kaper]

You may try:

Please Login or Register  to view this content.

it consists of 2 parts - counting days not on holiday in given daterange for given person:

Please Login or Register  to view this content.

and counting days not on holiday in given daterange for all teammembers:

Please Login or Register  to view this content.

You may use ROUND to have whole figures with no decimal parts, but then I'd use in last cell (B17):

Please Login or Register  to view this content.

[Andylmarks]

Thank you so much that works

[kvsrinivasamurthy]

This single formula works.
ARRAY formula in B10 then copy down.

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[Andylmarks]

Thank you