Hi
I need some help with a formula. For example I need to calculate the number of working days in May 2016 within the data shown below. I have a formula which is not giving me the correct answer. The answer should be 64 days. Sample file is attached for reference. The formula I need help with is:

=SUMPRODUCT(--(MONTH($F$1)&"|"&YEAR($F$1)=MONTH(Sheet1!$E$6:$E$4 000)&"|"&YEAR(Sheet1!$E$6:$E$4000)),--($A5=Sheet1!$A$6:$A$4000),Sheet1!$I$6:$I$4000)

Col A ......................Col E......Col F
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 23/05/16 03/06/16
BUSINESS SUPPORT 25/04/16 22/06/16
BUSINESS SUPPORT 25/07/16 25/07/16

Any help would be appreciated.

Kind regards

Tony

Attached Files

Original Res Pool Availability Gantt Chart Template.xlsm (89.0 KB, 11 views)

Can you explain how you would get a result of 64 with the sample you provided in your workbook?

Hi
I have just checked the figures for working days for May 17 should be:
13 Apr 16 to 19 May 17 = 15 days
9 Feb 16 to 18 May 17 = 14 days
2 Aug 16 - 15 May 17 = 11 days
22 May 17 to 24 May 17 = 3 days
23 May 16 to 23 May 17 = 17 days
10 Feb 17 to 9 Jun 17 = 23 days
22 May 17 to 26 May 17 = 5 days
29 May 17 - 2 Jun 17 = 3 days
29 May 17 to 2 Jun 17 = 3 days
Totalling: 94 days - sorry for the error.

Thanks

Tony

I don't see any of those dates in your sample file. I am trying to figure out why your formula is not working (to me it seems correct based on the logic I am assuming you are looking for) but I can't determine that if I don't match your expected results to your formula output.

Hi
Have a look at a revised sample attached.

Thanks for looking at my issue/challenge.

Ignore then entries in B1:C1.

Tony

Attached Files

Sumproduct Challenge.xlsx (12.0 KB, 15 views)

The thing is that if those entries were in your original sheet, I think your formula (that you already have there) would give you the correct result. Unless the setup of your workbook is not like your original sample.

I am confused as to why you think that the formula is wrong.

Hi
The NETWORKDAYS formula in the Sumproduct Challenges workbook only works for a single row. I need it to work for a range. For example I need the number of working days in May 2017 where the row in Column A = "Business Support". That's the bit I am struggling with.
Thanks
Tony

Why not use the calculated values in column D as you did in your original formula?

Hi
The solution needs to cover a range of dates, not just May. My approach was that if I had the solution for May I could use the same formula for other months.
For example I will have a range of dates from Feb 16 to Dec 17 and I need to calculate the number of days within each date range for each month.
Hope this makes sense.
Thanks
Tony

Does this array formula work?

=SUM((NETWORKDAYS(IF(D1>$B$3:$B$11,D1,B3:B11),IF(E OMONTH(D1,0)<C3:C11,EOMONTH(D1,0),$C$3:$C$11))))

Confirmed with CTRL+SHIFT+ENTER not just ENTER

Hi
Your suggested formula does not take into account the skillset in Column A which I need it to.
Thanks for your help so far.
It is really appreciated.
Kind regards
Tony

Sorry about that... try:

Confirmed with CTRL+SHIFT+ENTER

You can replace "Business Support" with a reference cell containing that text string...

Hi
Works perfectly.
Many thanks for your help.
Kind regards
Tony