Sum Dynamic Range
 

Hello,

I know there are several posting regarding this topic already but they
don't seem to address what i trying to do. Can someone help.

In column A, I have a list of tasks. These tasks are being pull from a
DB so they can shrink or grow. These tasks are arranged so that all
the ones related to the same project are displayed in order (a1, a2,
a3, etc etc). After the last task, there is a total for that project.

In column B, I have the associated cost for each of those tasks.

My problem is, without using VB, how can i write a formula to
dynamically sum up the dollar for each project? For instance, let's
say B2:B9 are tasks and B10 is where the total is. I need B10 to
dynamically add up all costs B2:B9. Again, keep in mind that the next
project could be only 2 tasks and so the next total row (B13), will be
suming B11:B12.

Any help is greatly appreciated.

Sum Dynamic Range
 

LP submitted this idea :
Hello,

I know there are several posting regarding this topic already but they
don't seem to address what i trying to do. Can someone help.

In column A, I have a list of tasks. These tasks are being pull from a
DB so they can shrink or grow. These tasks are arranged so that all
the ones related to the same project are displayed in order (a1, a2,
a3, etc etc). After the last task, there is a total for that project.

In column B, I have the associated cost for each of those tasks.

My problem is, without using VB, how can i write a formula to
dynamically sum up the dollar for each project? For instance, let's
say B2:B9 are tasks and B10 is where the total is. I need B10 to
dynamically add up all costs B2:B9. Again, keep in mind that the next
project could be only 2 tasks and so the next total row (B13), will be
suming B11:B12.

Any help is greatly appreciated.

Try this...

=SUMIF($A:$A,"TaskName",$B:$B)

...where "TaskName" is the expected value to be found in columnA.

Optionally, you could create a defined name range ref to the task name
located in the cell above and to the left of the cell that contains the
formula. This would allow using the same formula for all cells that sum
the total. For example, using your sample cell refs select cell B10,
open the Defined Name dialog and enter the following:

In the name box:
'SheetName'!TaskName
where the actual tab name of the worksheet is entered wrapped in
apostrophes and followed by the exclamation character. "TaskName" will
be the Defined Name used in the formula. This defined name will be
'local' to the sheet it's defined on. This means you can use this same
name without conflict on other sheets in the same workbook.

In the RefersTo box:
=$A9
where the ref to columnA is absolute and the ref to the row is the row
before the cell containing the formula.

In all cells you want to contain totals:
=SUMIF($A:$A,TaskName,$B:$B)
where the formula reads the task name in colA of the cell above, looks
for all occurances of this in colA and sums the corresponding amount in
colB for each occurance it finds.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Sum Dynamic Range
 

Anything about these tasks that make them unique to a group?

Maybe DataSubtotalsAt each change in......would do the trick.


Gord Dibben MS Excel MVP

On Thu, 2 Dec 2010 23:03:03 -0800 (PST), LP wrote:

Hello,

I know there are several posting regarding this topic already but they
don't seem to address what i trying to do. Can someone help.

In column A, I have a list of tasks. These tasks are being pull from a
DB so they can shrink or grow. These tasks are arranged so that all
the ones related to the same project are displayed in order (a1, a2,
a3, etc etc). After the last task, there is a total for that project.

In column B, I have the associated cost for each of those tasks.

My problem is, without using VB, how can i write a formula to
dynamically sum up the dollar for each project? For instance, let's
say B2:B9 are tasks and B10 is where the total is. I need B10 to
dynamically add up all costs B2:B9. Again, keep in mind that the next
project could be only 2 tasks and so the next total row (B13), will be
suming B11:B12.

Any help is greatly appreciated.

Sum Dynamic Range
 

Awesome ! thank you everyone !



On Dec 3, 9:09*am, Gord Dibben wrote:
Anything about these tasks that make them unique to a group?

Maybe DataSubtotalsAt each change in......would do the trick.

Gord Dibben * * MS Excel MVP



On Thu, 2 Dec 2010 23:03:03 -0800 (PST), LP wrote:
Hello,

I know there are several posting regarding this topic already but they
don't seem to address what i trying to do. Can someone help.

In column A, I have a list of tasks. These tasks are being pull from a
DB so they can shrink or grow. These tasks are arranged so that all
the ones related to the same project are displayed in order (a1, a2,
a3, etc etc). *After the last task, there is a total for that project.

In column B, I have the associated cost for each of those tasks.

My problem is, without using VB, how can i write a formula to
dynamically sum up the dollar for each project? For instance, let's
say B2:B9 are tasks and B10 is where the total is. *I need B10 to
dynamically add up all costs B2:B9. *Again, keep in mind that the next
project could be only 2 tasks and so the next total row (B13), will be
suming B11:B12.

Any help is greatly appreciated.- Hide quoted text -

- Show quoted text -

Sum Dynamic Range
 

I was think also that you could make this more intuitive as well as
more efficient by locally defining a named formula that you could use
in any column, based on group names being listed in colA.

1. Select a cell in ColB.

2. In the name box:
'SheetName'!GroupTotal

3. In the RefersTo box:
=SUMIF($A:$A,TaskName,B:B)

...where the formula will automatically adjust to collect amounts in
whatever column you want group totals for. Note that this formula uses
the defined name I posted yesterday.

Simply enter into cells where you want totals:
=GroupTotal

Note that this formula doesn't require grouping and so task names can
be in any order.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc