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.

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))

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))

This is very nice, Glenn. So clean and I like the use of <.
This assumes the following:
BA will calc everything that is not X, including blanks or unspecified
cells. Can we either not include cells that are blank or not calc until all
cells are not blank?
BB will calc everything that is R - can we make it so that a P calc 1/2 time
too?

Either way, thank you for your time, insights, and formula.

Eric


"Glenn" wrote:

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))
.

See below.


ewillig wrote:
This is very nice, Glenn. So clean and I like the use of <.
This assumes the following:
BA will calc everything that is not X, including blanks or unspecified
cells.
Can we either not include cells that are blank


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


or not calc until all cells are not blank?


=IF(COUNTA(M3:AZ3)<40,"",SUMPRODUCT((M3:AZ3<"X")* ($M$2:$AZ$2*$L3)))


BB will calc everything that is R - can we make it so that a P calc 1/2 time
too?


=SUMPRODUCT((M3:AZ3="R")*($M$2:$AZ$2*$L3))+SUMPROD UCT((M3:AZ3="P")*($M$2:$AZ$2*$L3))/2



Either way, thank you for your time, insights, and formula.

Eric


"Glenn" wrote:


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))
.



Glenn wrote:

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))


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.

Glenn, this is perfect. Thank you for your time and expertise.

Best,

Eric



"Glenn" wrote:

See below.


ewillig wrote:
This is very nice, Glenn. So clean and I like the use of <.
This assumes the following:
BA will calc everything that is not X, including blanks or unspecified
cells.
Can we either not include cells that are blank


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


or not calc until all cells are not blank?


=IF(COUNTA(M3:AZ3)<40,"",SUMPRODUCT((M3:AZ3<"X")* ($M$2:$AZ$2*$L3)))


BB will calc everything that is R - can we make it so that a P calc 1/2 time
too?


=SUMPRODUCT((M3:AZ3="R")*($M$2:$AZ$2*$L3))+SUMPROD UCT((M3:AZ3="P")*($M$2:$AZ$2*$L3))/2



Either way, thank you for your time, insights, and formula.

Eric


"Glenn" wrote:


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))
.



Glenn wrote:

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))


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.


.