Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct Q | Excel Worksheet Functions | |||
using SUMPRODUCT | Excel Worksheet Functions | |||
how to use sumproduct | Excel Worksheet Functions |