Prev: Why won't text show in SOME cells?
Next: @Count
From: Glenn on 7 Apr 2010 13:26
Glenn wrote:
> ewillig wrote:
>> Rows = projects and Columns = components needed for this project. I
>> am looking to make 2 calculations on each project. The first is the
>> overall time required for this project based upon the components
>> required for this project and the second is the balance of time
>> required to complete it.
>>
>> In row 2 I have a series of base values - one each for 40 columns
>> (components) starting at column M
>> In left most column (column L) I have a multiplier picklist (1,2, or
>> 3) created using data validation
>> In each row after row 2 and to the right of column L, I have another
>> picklist of (X, R, P, C where X is chosen if this component is not
>> needed for this project; R indicated a required component; P indicates
>> a required component which is already in process of being completed;
>> and C indicated a component which is required that has already been
>> completed.
>>
>> At the far right, after our 40 columns representing the 40 possible
>> project components, I have 2 more columns. The first, column BA, is
>> where I am looking to total the amount time required for this project
>> (in hours or minutes) based upon the components needed*the base
>> variable for that component (row 2)*difficulty of that project (column
>> L). In the second column, column BB, I am looking for a similiar
>> calcualtion but this one totals the balance of the time needed to
>> complete this project (not the total time but the time required to
>> complete the balance of the components - not started or already
>> completed).
>>
>> I know that I can create custom functions for each of the 40 columns
>> and combine them in a formula but that is both time consuming and
>> cumbersome. I am hoping there is a more elegant formula that can be
>> used.
>>
>> Thank you for your time and consideration.
>>
>
>
> In column BA:
>
> =SUMPRODUCT((M3:AZ3<>"X")*($M$2:$AZ$2*$L$3))
>
> In column BB:
>
> =SUMPRODUCT((M3:AZ3="R")*($M$2:$AZ$2*$L$3))


Sorry, too many dollar signs.

BA:

=SUMPRODUCT((M3:AZ3<>"X")*($M$2:$AZ$2*$L3))

BB:

=SUMPRODUCT((M3:AZ3="R")*($M$2:$AZ$2*$L3))
 | 
Pages: 1
Prev: Why won't text show in SOME cells?
Next: @Count