Sumproduct
 

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!

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!