tricky sum problem

[Dave F]

I have a range of cells, E6475, 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

 

[Elkar]

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(E5959,"project",E7676)

=SUMIF(E5959,"BAU",E7676)

HTH,
Elkar

 

[Ken Johnson]

I have a range of cells, E6475, 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

Hi Dave,

=SUMPRODUCT((E6575)*(E5959="project"))
=SUMPRODUCT((E6575)*(E5959="BAU"))

Ken Johnson

 

[Dave F]

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

 

[Dave F]

That looks simple enough, thanks.

Dave

 

[Ken Johnson]

You're welcome Dave.
Thanks for the feedback.

Ken Johnson

 

[Dave F]

Just tested this and it works perfectly, thanks.

Dave