Embedding MAX/MIN in SUMPRODUCT Formula

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!

Try this.

Formula:

Please Login or Register  to view this content.

Thank you, DJunqueira, but it doesn't look like that achieves the desired output. I'm confident about this part of the formula to find the overlapping workdays: =MAX(NETWORKDAYS(MAX(start1,start2), MIN(end1,end2), tblHolidays),0).
Again, much appreciate the assist.

Hi Ali,

Thank you for coaching me through this! First, I'm going to apologize, and then I'm going to hide under a rock because my manual calculations for Contract 1 were wrong.

Please take a look at my revised file attached.

The formula for a single opportunity is (Rev/hr) * (8 hrs/day) * the overlapping workdays between the start and end of the opportunity and the calendar month; this formula is correctly represented in B30:M31.

However, I want the summary table (B17:M19) to show the sum of all opportunities for a given contract in the date range.

Thank you again for your kind assistance.

Respectfully and gratefully,
steve

Hi Ali,

I'm sorry for being the source of the flummoxing and much appreciate your continued willingness to assist.

There are two separate opportunities associated w/ Contract #1. Those could be additional people, work, scope, etc., where the revenue is based on working hrs. In real life, these numbers would be much higher, but used $1 and $2 for simplicity sake.

MY GOAL: Calculate the sum of all opportunities, per contract, per month in Table B17:M19 (without the use of a helper table).

Contract #1, Opportunity #1 is $1/hr, per working hr, from 1 Jan - 6 Jul. There are 8 working hrs each day, M-F, subtract the holiday table.
So, for Jan - Jun, the amt of monthly revenue associated w/ Contract #1, Opportunity #1 equals the number of workdays in ea month * 8 hrs * $1/hr
But for Jul, there is only a 4-workday overlap w/ the dates associated w/ Opp #1, so revenue = 4 (overlapping workdays) * 8hrs * $1/hr = $32

Contract #2, Opportunity #2 is $2/hr, per working hr, from 1 May - 9 Dec.
And so, for May - Nov, the amt of monthly revenue associated w/ Contract #1, Opportunity #2 equals the number of workdays in ea month * 8 hrs * $2/hr
But for Dec, there is only a 6-workday overlap w/ the dates associated w/ Opp #2, so revenue = 6 (overlapping workdays) * 8hrs * $2/hr = $96

I'm confident I have correctly captured in B30 (and copied to M31) the formula for the amount of revenue for any given month, associated with a SINGLE opportunity, but I want the table in B17:M19 to capture the total of ALL opportunities per contract (per month), assuming the table A3:D6 is much larger in real life, covering hundreds of opportunities across many contracts.

My (non-working) formula in B17 tries to achieve this aggregation by attempting (but failing) to SUMPRODUCT the formula that represents the revenue associated w/ a single opportunity (B30) w/ a lookup against the Contract name (A3:A6) and the start and end dates of ea month (B14:M15).

Thank you very much.

steve

Thanks so much, but I can't follow any of that.

Thank you - I don't understand any of it, but it works.