Sumproduct
Why don't you use
=SUMPRODUCT(--(A3:A10="Cap"),(C3:C10)*(B3:B10))+SUMPRODUCT(--(A3:A10="Cap"),(D3:D10)*(B3:B10))+SUMPRODUCT(--(A3:A10="Cap"),(E3:E10)*(B3:B10))
Each sumproduct gives you the total for APR, MAY and JUN for Cap...
"lisay" wrote:
Hi,
I am trying to calculate monthly cost based on the following table
Phase --- 1.0 Design 2.0 Development 3.0 Testing 4.0
Deployment
Rate Apr 09 May 09 June 09
July 09
Cap 20 50% 15% 10%
10%
Cap 50 100% 100% 50%
20%
Exp 20 100% 80% 80%
50%
So, If Col1 = Cap AND phase is (1.0 Design OR 2.0 Development OR 3.0 Test),
do a sumproduct of Rate & % of each month.
Result should be
Apr 09 = 10
May 09 = 3 and so on...
but Month July = 0 because phase is not one of the 3 phases.
Your help would be much appreciated. Thank you!
|