sum a column within a name definition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LaurenA - 12 May 2008 20:10 GMT

I have several names defined for different cell ranges and I would like to be
able to sum up the values in Column C within a particular name definition.

For example, if a name is defined for range A2:J13, I would like to be able
to sum up column C's values without have to explicitly type C2:C13.

Any ideas of how to go about this?

Reply to this Message

Imonit - 12 May 2008 20:41 GMT

Hello there. I believe you can do as such....

=sum(SumMe)

SumMe being the range of C2:C13 or whatever else you wish to call it.

Hope that helps!

-Imonit

> I have several names defined for different cell ranges and I would like to be
> able to sum up the values in Column C within a particular name definition.
[quoted text clipped - 3 lines]
>
> Any ideas of how to go about this?

Reply to this Message

LaurenA - 12 May 2008 21:12 GMT

Thanks for the reply.

I'd rather not define the range C2:C13 explicitly if possible. Maybe I can
describe my problem in more detail and you can see why this would be
difficult.

I have several cells which are defined by names on a worksheet (about 60 in
total).
"NameA" A2:X13
"NameB" A15:X25
"NameC" A27:X39
.
.
.

I would like to create a table that summarizes the sums as such below (where
the Xs represent the column sums for the particular name listed at left):

Col_A_Tot Col_B_Tot Col_C_Tot ....
NameA X X X
NameB X X X
NameC X X X
.
.
.

If I could find a formula to help with this, I could make this table dynamic
and save myself lots of manual input.

Thanks!!

> Hello there. I believe you can do as such....
>
[quoted text clipped - 13 lines]
> >
> > Any ideas of how to go about this?

Reply to this Message

ShaneDevenshire - 13 May 2008 03:11 GMT

Hi Lauren,

Suppose you name the entire range D, where D is the range C3:H14, then:

=SUM(OFFSET(D,0,3,12,1))

Will sum column F within the range D, that is F3:F14. The 0 means we want
to start on the first row of the range D, 3 says we want to start 3 columns
to the right of C, 12 says the hight of the range we want to sum is 12 rows
and 1 tells Excel we want to some a range 1 column wide.

Signature

Cheers,
Shane Devenshire
Microsoft Excel MVP

> Thanks for the reply.
>
[quoted text clipped - 44 lines]
> > >
> > > Any ideas of how to go about this?

Reply to this Message

ShaneDevenshire - 13 May 2008 04:41 GMT

Hi Lauren,

I used all the arguments in my example to show how they are used

You could modify the formula to read:

=SUM(OFFSET(D,,2,,1))

If D is the name of the range, any range, even A2:J13, then this formula
will sum the third column or C2:C13 of your range.

Signature

Cheers,
Shane Devenshire
Microsoft Excel MVP

> Hi Lauren,
>
[quoted text clipped - 55 lines]
> > > >
> > > > Any ideas of how to go about this?

Reply to this Message

T. Valko - 13 May 2008 03:35 GMT

Try this:

=SUM(INDEX(NameA,,3))

That will sum C2:C13.

Signature

Biff
Microsoft Excel MVP

> Thanks for the reply.
>
[quoted text clipped - 51 lines]
>> >
>> > Any ideas of how to go about this?

Reply to this Message

LaurenA - 19 May 2008 02:41 GMT

Thank you!

> Try this:
>
[quoted text clipped - 57 lines]
> >> >
> >> > Any ideas of how to go about this?

Reply to this Message

T. Valko - 19 May 2008 03:30 GMT

You're welcome!

Signature

Biff
Microsoft Excel MVP

> Thank you!
>
[quoted text clipped - 63 lines]
>> >> >
>> >> > Any ideas of how to go about this?

Reply to this Message