Help with a SUMPRODUCT Formula
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
|