Hi All
I thought Jeffrey Brown had resolved my issue but it does not appear to be so (sorry Jeffrey). I attach a sample workbook detailing my issue.
If you look at the second sheet in the workbook I have annotated what the figures should be for each month.


Any help appreciated.
Thanks in anticipation.
Tony

Edit Mod: thread relatted to https://www.msofficeforums.com/excel...lp-sumifs.html

Attached Files

Sample Resource Pool Availability Template.xlsm (81.9 KB, 15 views)

I'm afraid I do not understand how you come up with your figures...

If you could explain how you get...

I see two Apr 16 in Business Support which would total -- >> 26

I see one May 16 in Enterprise Information Mgmt which would total -- >> 12

There are just three inputs and no Jun 16

How do you get 44 for Business support in May 16 and how do you get anything at all for Jun 16 when there is no Jun 16?

Hi Jeffrey
Sorry, my calculations were not completely correct. Please see revised sample workbook.
Basically I need to calculate the number of working days for each month for each Skillset between the Start and End dates for each activity on sheet 1.
Hope this helps.
Kind regards
Tony

Attached Files

Sample Resource Pool Availability Template.xlsm (82.0 KB, 13 views)

Tony,

Before I begin to craft a formula for your query, please explain to me how you arrive at 8 and 19 for J7 and J8 respectively?

Hi Jeffrey
J8:
For the task on Sheet1!Row 5 the number of working days between 1 June 16 and the task end date is "3".
Then I add the number of working days between 1 June and the task end date for Sheet 1!Row 6 which is "16".
This makes a total of 19 days for Business Support for June.
J7:
For the task on Sheet1!Row 7 the number of working days between 1 June and the task end date is "8".
This makes a total of 8 working days in June.
Hope this helps.
Thanks Jeffrey.
Tony

Okay, I can see what you are asking now, but I wish you had told me you were shifting gears in your quest for a different answer from the previous answer in post #7.

I'm afraid I do not have an answer for this query, but hopefully with a bump to the top somebody else can step in.

BTW: Unless I am just totally off, not sure how you get "16" from 1 Jun to 22 Jun?

Maybe this should have been a separate thread as it changed directions.

Hi Jeffrey
Apologies for not making my "challenge" clearer in the first place.
The number of working days between the 1st and 22nd June is 16 (excluding weekends).
many thanks for your help to date.
Tony

ps: I just stumbled across the www.cpearson.com website which gives me a solution for a single row as follows:
=MAX(0,NETWORKDAYS(MAX(C$1,Sheet1!$E5),MIN(DATE(YE AR(C$1),MONTH(C$1)+1,0),Sheet1!$F5)))
This works exactly as I need it but my challenge is how to wrap this in a SUMPRODUCT function so I get a total for all skillsets.
Any help appreciated Jeffrey.
Thanks
Tony