Re: sum a column within a name definition

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP


"ShaneDevenshire" wrote:

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.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"LaurenA" wrote:

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




"Imonit" wrote:

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





On May 12, 3:10 pm, LaurenA <Laur...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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?


.

Relevant Pages

  • Re: How can I vary the row number or column alphabet depending on
    ... Microsoft Excel MVP ... Valko" wrote: ... To sum B1:D1 ... Note that if cell C1 is empty the *entire* range will be calculated. ...
    (microsoft.public.excel.misc)
  • Re: Subrange
    ... Microsoft Excel MVP ... A without first row". ... sum of negative increments: ... sum of positive and negative increments. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Sumif
    ... Ok, syntactically, there's nothing wrong with that test formula. ... Microsoft Excel MVP ... Sum If Sheet2!B3:B18000 = Sheet1!A3 ... the criteria was found. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Sumif
    ... Microsoft Excel MVP ... Sum If Sheet2!B3:B18000 = Sheet1!A3 ... the criteria was found. ... use a standard Sumif, with the column to search being Sheet2! ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Time and Sum if
    ... To sum for a specific single date: ... Microsoft Excel MVP ... sumif a criteria is met. ... So for arguments sake, if the date/time is in Column A, and the item I ...
    (microsoft.public.excel.misc)