Sumifs Function issue

Hi There,

I have a file (attached) that shows data for a specific Client that was involved in many Contracts during the period 2012-2016. for eact of the contracts we have the revenue, profit in $ and the profit in %. I have tried using sumifs function to populate the summary tab table.
I have a dropdown for the years and contract.

for "Reported Revenue" I would like to retrieve numbers from the details tab based on the dropdowns. I used the following formula:

=IF($C$5="All",IF($C$9="All",SUM(Details!$G:$G),SUMIFS(Details!$G:$G,Details!$F:$F,Summary!$C$5,Details!$D:$D,Summary!$C$9,Details!$B:$B,Summary!$C$7)))

This works only when "year" dropdown = All and "Contract" dropdown = All.

Ideally I would like to run a number of scenrios where I would like to calculate the revenue:
e.g
Year = 2012, Contract = Contract N
Year = 2015, Contract = All
etc.

Another issue is that the profit % is calculated by =sum(profit $/revenue), which is already done in the details tab for each contract (col I). I would like to do the same as above for the profit %. Also when selecting All, the formula will have to include a calculation to sum up revenue for all years and profit $ for all years or for each of those individual year.

I am have trouble with capturing the correct formula to do this peice of analysis.

Much Appreciated

KR
F

Try this.
=SUMIFS(Details!$G:$G,Details!$F:$F,IF($C$5="All","*",$C$5),Details!$D:$D,IF($C$9="All","*",$C$9),Details!$B:$B,$C$7)

For Revenue

=SUMIFS(Details!$G:$G,Details!$F:$F,IF($C$5="All","*",$C$5),Details!$D:$D,IF($C$9="All","*",$C$9),Details!$B:$B,Summary!$C$7)

For Profit %:

=SUMIFS(Details!$H:$H,Details!$F:$F,IF($C$5="All","*",$C$5),Details!$D:$D,IF($C$9="All","*",$C$9),Details!$B:$B,Summary!$C$7)/SUMIFS(Details!$G:$G,Details!$F:$F,IF($C$5="All","*",$C$5),Details!$D:$D,IF($C$9="All","*",$C$9),Details!$B:$B,Summary!$C$7)

Thanks CK76 & John, the Revenue formula works like a charm . Amazing stuff