Greetings,
Apologies for struggling with a properly descriptive title for this post.
I'm trying to tabulate the sum of my opportunities (A3:D6) by contract and month.
The dollar value of an opportunity is calculated as the the $/hr * workdays that the opportunity overlaps with the given month * 8 hrs.
The desired results are in the bottom table.
In cell B17 I have this formula, which I think is pretty close, but is not working:
=SUMPRODUCT((MAX(NETWORKDAYS(MAX(B$14,$C$3:$C$6), MIN(B$15,$D$3:$D$6), $H$2:$H$12),0))*($A$3:$A$6=$A17)*($B$3:$B$6)*8)
For what it's worth, the formula below to find the number of overlapping workdays between two sets of dates, seems to work as intended:
=MAX(NETWORKDAYS(MAX(start1,start2), MIN(end1,end2), tblHolidays),0)
Thank you for the help!
Bookmarks