tricky sum problem
 

I have a range of cells, E64:P75, which need to be summed. Each column of
cells in this range (i.e., E64:E75, F64:F75, etc.) is categorized as either
"project" or "BAU"

I want two sums calculated from this range: total project and total BAU.
Here's the tricky part: the categorization of a particular column in this
range as either "project" or "BAU" is in row 59.

How can I do this? I can, in theory do the sum by color routine, as
conditional formatting has been applied to this range based on the
categorizations explained above. However, I would like to avoid VBA, as my
boss seems suspicious of it.

Any thoughts on how to do this with formulas?

Thanks,

Dave
--
Brevity is the soul of wit.

tricky sum problem
 

You could add a new row (let's say 76), then use this row to perform a SUM on
each individual column. Then, use the SUMIF function to find your totals.

=SUMIF(E59:P59,"project",E76:P76)

=SUMIF(E59:P59,"BAU",E76:P76)

HTH,
Elkar


"Dave F" wrote:

I have a range of cells, E64:P75, which need to be summed. Each column of
cells in this range (i.e., E64:E75, F64:F75, etc.) is categorized as either
"project" or "BAU"

I want two sums calculated from this range: total project and total BAU.
Here's the tricky part: the categorization of a particular column in this
range as either "project" or "BAU" is in row 59.

How can I do this? I can, in theory do the sum by color routine, as
conditional formatting has been applied to this range based on the
categorizations explained above. However, I would like to avoid VBA, as my
boss seems suspicious of it.

Any thoughts on how to do this with formulas?

Thanks,

Dave
--
Brevity is the soul of wit.

tricky sum problem
 

Dave F wrote:
I have a range of cells, E64:P75, which need to be summed. Each column of
cells in this range (i.e., E64:E75, F64:F75, etc.) is categorized as either
"project" or "BAU"

I want two sums calculated from this range: total project and total BAU.
Here's the tricky part: the categorization of a particular column in this
range as either "project" or "BAU" is in row 59.

How can I do this? I can, in theory do the sum by color routine, as
conditional formatting has been applied to this range based on the
categorizations explained above. However, I would like to avoid VBA, as my
boss seems suspicious of it.

Any thoughts on how to do this with formulas?

Thanks,

Dave
--
Brevity is the soul of wit.
Hi Dave,

=SUMPRODUCT((E65:P75)*(E59:P59="project"))
=SUMPRODUCT((E65:P75)*(E59:P59="BAU"))

Ken Johnson

tricky sum problem
 

Thanks. I thought of this, however, the way the workbook is formatted I'm
not sure I can add the extra row. But thanks.
--
Brevity is the soul of wit.


"Elkar" wrote:

You could add a new row (let's say 76), then use this row to perform a SUM on
each individual column. Then, use the SUMIF function to find your totals.

=SUMIF(E59:P59,"project",E76:P76)

=SUMIF(E59:P59,"BAU",E76:P76)

HTH,
Elkar


"Dave F" wrote:

I have a range of cells, E64:P75, which need to be summed. Each column of
cells in this range (i.e., E64:E75, F64:F75, etc.) is categorized as either
"project" or "BAU"

I want two sums calculated from this range: total project and total BAU.
Here's the tricky part: the categorization of a particular column in this
range as either "project" or "BAU" is in row 59.

How can I do this? I can, in theory do the sum by color routine, as
conditional formatting has been applied to this range based on the
categorizations explained above. However, I would like to avoid VBA, as my
boss seems suspicious of it.

Any thoughts on how to do this with formulas?

Thanks,

Dave
--
Brevity is the soul of wit.

tricky sum problem
 

That looks simple enough, thanks.

Dave
--
Brevity is the soul of wit.


"Ken Johnson" wrote:


Dave F wrote:
I have a range of cells, E64:P75, which need to be summed. Each column of
cells in this range (i.e., E64:E75, F64:F75, etc.) is categorized as either
"project" or "BAU"

I want two sums calculated from this range: total project and total BAU.
Here's the tricky part: the categorization of a particular column in this
range as either "project" or "BAU" is in row 59.

How can I do this? I can, in theory do the sum by color routine, as
conditional formatting has been applied to this range based on the
categorizations explained above. However, I would like to avoid VBA, as my
boss seems suspicious of it.

Any thoughts on how to do this with formulas?

Thanks,

Dave
--
Brevity is the soul of wit.
Hi Dave,

=SUMPRODUCT((E65:P75)*(E59:P59="project"))
=SUMPRODUCT((E65:P75)*(E59:P59="BAU"))

Ken Johnson

tricky sum problem
 

You're welcome Dave.
Thanks for the feedback.

Ken Johnson

tricky sum problem
 

Just tested this and it works perfectly, thanks.

Dave
--
Brevity is the soul of wit.


"Ken Johnson" wrote:


You're welcome Dave.
Thanks for the feedback.

Ken Johnson