Hi,

I am having trouble trying to work out how to sum some non-contiguous ranges 
in Excel 2000.

The data is on sheet 1.  It is budget data for a number of employees.
There are 12 cells (one for each month), for four different categories, for 
each employee.

Eg, the category 1 (billing) data for employee 1 would have a range 
something like:
d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
category 2 is one column over and would be...
e6;e22;e46;i6;i22 etc.

and so on for each category for each of 14 or so employees.

To make this more difficult, because employees are constantly changing, 
there is no clear pattern to the rows that belong to each employee.

Is there an easy way for me to sum these ranges without having to click on 
each and every cell holding down CTRL?  If I do this, and someone deletes a 
row - it would wreck the totals, wouldn't it?

If I could have a list of the row numbers for each employee, and the column 
letters, and could combine these into cell references that would be great, 
but I'm not sure if that is possible.

Does anyone have any suggestions how I could do this?

Cheers,
Caroline (COE)

It is difficult to imagine that this is possible if there is no pattern. Is
there not any details in other cells that might indicate which cells to SUM?

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"COE" <COE@discussions.microsoft.com> wrote in message
news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> Hi,
>
> I am having trouble trying to work out how to sum some non-contiguous
ranges
> in Excel 2000.
>
> The data is on sheet 1.  It is budget data for a number of employees.
> There are 12 cells (one for each month), for four different categories,
for
> each employee.
>
> Eg, the category 1 (billing) data for employee 1 would have a range
> something like:
> d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> category 2 is one column over and would be...
> e6;e22;e46;i6;i22 etc.
>
> and so on for each category for each of 14 or so employees.
>
> To make this more difficult, because employees are constantly changing,
> there is no clear pattern to the rows that belong to each employee.
>
> Is there an easy way for me to sum these ranges without having to click on
> each and every cell holding down CTRL?  If I do this, and someone deletes
a
> row - it would wreck the totals, wouldn't it?
>
> If I could have a list of the row numbers for each employee, and the
column
> letters, and could combine these into cell references that would be great,
> but I'm not sure if that is possible.
>
> Does anyone have any suggestions how I could do this?
>
> Cheers,
> Caroline (COE)
>

Hi Bob,

Each employee has between 1-3 rows of information, but there isn't a pattern 
to these row numbers that covers all employees.

If I could just enter this row information in some cells, and the columns 
into another cell and combine these into cell references that I could use, 
then I would be rolling, but I don't know if that is possible.

eg. Employee 1 has rows 6, 22, 46.
Employee 2 - 7, 23, 47
Employee 3 - 11
Employee 4 - 12, 28, 52

Somehow I'm getting a feeling there is no easy way to do this,
COE
"Bob Phillips" wrote:

> It is difficult to imagine that this is possible if there is no pattern. Is
> there not any details in other cells that might indicate which cells to SUM?
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "COE" <COE@discussions.microsoft.com> wrote in message
> news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> > Hi,
> >
> > I am having trouble trying to work out how to sum some non-contiguous
> ranges
> > in Excel 2000.
> >
> > The data is on sheet 1.  It is budget data for a number of employees.
> > There are 12 cells (one for each month), for four different categories,
> for
> > each employee.
> >
> > Eg, the category 1 (billing) data for employee 1 would have a range
> > something like:
> > d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> > category 2 is one column over and would be...
> > e6;e22;e46;i6;i22 etc.
> >
> > and so on for each category for each of 14 or so employees.
> >
> > To make this more difficult, because employees are constantly changing,
> > there is no clear pattern to the rows that belong to each employee.
> >
> > Is there an easy way for me to sum these ranges without having to click on
> > each and every cell holding down CTRL?  If I do this, and someone deletes
> a
> > row - it would wreck the totals, wouldn't it?
> >
> > If I could have a list of the row numbers for each employee, and the
> column
> > letters, and could combine these into cell references that would be great,
> > but I'm not sure if that is possible.
> >
> > Does anyone have any suggestions how I could do this?
> >
> > Cheers,
> > Caroline (COE)
> >
> 
> 
> 

Well, for the time being I have decided to name all the ranges, using the 
Autofilter to make it easier to select all of the correct cells.

Maybe there is an easier way, but I guess it will be via VBA and I can't 
guarantee that there will be anyone in the office who will know even very 
simple VBA in the future.

So to keep it simple to maintain, I guess I'll have to put up with lots of 
named ranges.

COE

"COE" wrote:

> Hi Bob,
> 
> Each employee has between 1-3 rows of information, but there isn't a pattern 
> to these row numbers that covers all employees.
> 
> If I could just enter this row information in some cells, and the columns 
> into another cell and combine these into cell references that I could use, 
> then I would be rolling, but I don't know if that is possible.
> 
> eg. Employee 1 has rows 6, 22, 46.
> Employee 2 - 7, 23, 47
> Employee 3 - 11
> Employee 4 - 12, 28, 52
> 
> Somehow I'm getting a feeling there is no easy way to do this,
> COE
> "Bob Phillips" wrote:
> 
> > It is difficult to imagine that this is possible if there is no pattern. Is
> > there not any details in other cells that might indicate which cells to SUM?
> > 
> > -- 
> >  HTH
> > 
> > Bob Phillips
> > 
> > (remove nothere from email address if mailing direct)
> > 
> > "COE" <COE@discussions.microsoft.com> wrote in message
> > news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> > > Hi,
> > >
> > > I am having trouble trying to work out how to sum some non-contiguous
> > ranges
> > > in Excel 2000.
> > >
> > > The data is on sheet 1.  It is budget data for a number of employees.
> > > There are 12 cells (one for each month), for four different categories,
> > for
> > > each employee.
> > >
> > > Eg, the category 1 (billing) data for employee 1 would have a range
> > > something like:
> > > d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> > > category 2 is one column over and would be...
> > > e6;e22;e46;i6;i22 etc.
> > >
> > > and so on for each category for each of 14 or so employees.
> > >
> > > To make this more difficult, because employees are constantly changing,
> > > there is no clear pattern to the rows that belong to each employee.
> > >
> > > Is there an easy way for me to sum these ranges without having to click on
> > > each and every cell holding down CTRL?  If I do this, and someone deletes
> > a
> > > row - it would wreck the totals, wouldn't it?
> > >
> > > If I could have a list of the row numbers for each employee, and the
> > column
> > > letters, and could combine these into cell references that would be great,
> > > but I'm not sure if that is possible.
> > >
> > > Does anyone have any suggestions how I could do this?
> > >
> > > Cheers,
> > > Caroline (COE)
> > >
> > 
> > 
> > 

Assuming that D6:S100 contains your data...

1) Specify which rows to sum...

A1:

=CELL("row",D6)

A2:

=CELL("row",D22)

A3:

=CELL("row",D46)

Here, Rows 6, 22, and 46 will be summed.

2) Specify which category to sum...

B1:  1

1 equals Category 1 (Columns D, H, L, and P)

2 equals Category 2 (Columns E, I, M, and Q)

3 equals Category 3 (Columns F, J, N, and R)

4 equals Category 4 (Columns G, K, O, and S)

3) Then, try the following formula which needs to be confirmed with 
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(ISNUMBER(MATCH(ROW(D6:S100),A1:A3,0)),IF(MOD(COLUMN(D6:S100)-COLU
MN(D6),4)+1=B1,D6:S100)))

Note that it allows you to delete rows.

Hope this helps!

In article <BDEBCA08-DB7A-423D-B27A-3ABD761B1552@microsoft.com>,
 COE <COE@discussions.microsoft.com> wrote:

> Well, for the time being I have decided to name all the ranges, using the 
> Autofilter to make it easier to select all of the correct cells.
> 
> Maybe there is an easier way, but I guess it will be via VBA and I can't 
> guarantee that there will be anyone in the office who will know even very 
> simple VBA in the future.
> 
> So to keep it simple to maintain, I guess I'll have to put up with lots of 
> named ranges.
> 
> COE
> 
> "COE" wrote:
> 
> > Hi Bob,
> > 
> > Each employee has between 1-3 rows of information, but there isn't a 
> > pattern 
> > to these row numbers that covers all employees.
> > 
> > If I could just enter this row information in some cells, and the columns 
> > into another cell and combine these into cell references that I could use, 
> > then I would be rolling, but I don't know if that is possible.
> > 
> > eg. Employee 1 has rows 6, 22, 46.
> > Employee 2 - 7, 23, 47
> > Employee 3 - 11
> > Employee 4 - 12, 28, 52
> > 
> > Somehow I'm getting a feeling there is no easy way to do this,
> > COE
> > "Bob Phillips" wrote:
> > 
> > > It is difficult to imagine that this is possible if there is no pattern. 
> > > Is
> > > there not any details in other cells that might indicate which cells to 
> > > SUM?
> > > 
> > > -- 
> > >  HTH
> > > 
> > > Bob Phillips
> > > 
> > > (remove nothere from email address if mailing direct)
> > > 
> > > "COE" <COE@discussions.microsoft.com> wrote in message
> > > news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> > > > Hi,
> > > >
> > > > I am having trouble trying to work out how to sum some non-contiguous
> > > ranges
> > > > in Excel 2000.
> > > >
> > > > The data is on sheet 1.  It is budget data for a number of employees.
> > > > There are 12 cells (one for each month), for four different categories,
> > > for
> > > > each employee.
> > > >
> > > > Eg, the category 1 (billing) data for employee 1 would have a range
> > > > something like:
> > > > d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> > > > category 2 is one column over and would be...
> > > > e6;e22;e46;i6;i22 etc.
> > > >
> > > > and so on for each category for each of 14 or so employees.
> > > >
> > > > To make this more difficult, because employees are constantly changing,
> > > > there is no clear pattern to the rows that belong to each employee.
> > > >
> > > > Is there an easy way for me to sum these ranges without having to click 
> > > > on
> > > > each and every cell holding down CTRL?  If I do this, and someone 
> > > > deletes
> > > a
> > > > row - it would wreck the totals, wouldn't it?
> > > >
> > > > If I could have a list of the row numbers for each employee, and the
> > > column
> > > > letters, and could combine these into cell references that would be 
> > > > great,
> > > > but I'm not sure if that is possible.
> > > >
> > > > Does anyone have any suggestions how I could do this?
> > > >
> > > > Cheers,
> > > > Caroline (COE)
> > > >
> > > 
> > > 
> > >

Thanks Domenic,

I will have to try this out.  (Catering for the data being on a different 
sheet of the workbook to the calculation).

Cheers
COE

"Domenic" wrote:

> Assuming that D6:S100 contains your data...
> 
> 1) Specify which rows to sum...
> 
> A1:
> 
> =CELL("row",D6)
> 
> A2:
> 
> =CELL("row",D22)
> 
> A3:
> 
> =CELL("row",D46)
> 
> Here, Rows 6, 22, and 46 will be summed.
> 
> 2) Specify which category to sum...
> 
> B1:  1
> 
> 1 equals Category 1 (Columns D, H, L, and P)
> 
> 2 equals Category 2 (Columns E, I, M, and Q)
> 
> 3 equals Category 3 (Columns F, J, N, and R)
> 
> 4 equals Category 4 (Columns G, K, O, and S)
> 
> 3) Then, try the following formula which needs to be confirmed with 
> CONTROL+SHIFT+ENTER, not just ENTER...
> 
> =SUM(IF(ISNUMBER(MATCH(ROW(D6:S100),A1:A3,0)),IF(MOD(COLUMN(D6:S100)-COLU
> MN(D6),4)+1=B1,D6:S100)))
> 
> Note that it allows you to delete rows.
> 
> Hope this helps!
> 
> In article <BDEBCA08-DB7A-423D-B27A-3ABD761B1552@microsoft.com>,
>  COE <COE@discussions.microsoft.com> wrote:
> 
> > Well, for the time being I have decided to name all the ranges, using the 
> > Autofilter to make it easier to select all of the correct cells.
> > 
> > Maybe there is an easier way, but I guess it will be via VBA and I can't 
> > guarantee that there will be anyone in the office who will know even very 
> > simple VBA in the future.
> > 
> > So to keep it simple to maintain, I guess I'll have to put up with lots of 
> > named ranges.
> > 
> > COE
> > 
> > "COE" wrote:
> > 
> > > Hi Bob,
> > > 
> > > Each employee has between 1-3 rows of information, but there isn't a 
> > > pattern 
> > > to these row numbers that covers all employees.
> > > 
> > > If I could just enter this row information in some cells, and the columns 
> > > into another cell and combine these into cell references that I could use, 
> > > then I would be rolling, but I don't know if that is possible.
> > > 
> > > eg. Employee 1 has rows 6, 22, 46.
> > > Employee 2 - 7, 23, 47
> > > Employee 3 - 11
> > > Employee 4 - 12, 28, 52
> > > 
> > > Somehow I'm getting a feeling there is no easy way to do this,
> > > COE
> > > "Bob Phillips" wrote:
> > > 
> > > > It is difficult to imagine that this is possible if there is no pattern. 
> > > > Is
> > > > there not any details in other cells that might indicate which cells to 
> > > > SUM?
> > > > 
> > > > -- 
> > > >  HTH
> > > > 
> > > > Bob Phillips
> > > > 
> > > > (remove nothere from email address if mailing direct)
> > > > 
> > > > "COE" <COE@discussions.microsoft.com> wrote in message
> > > > news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I am having trouble trying to work out how to sum some non-contiguous
> > > > ranges
> > > > > in Excel 2000.
> > > > >
> > > > > The data is on sheet 1.  It is budget data for a number of employees.
> > > > > There are 12 cells (one for each month), for four different categories,
> > > > for
> > > > > each employee.
> > > > >
> > > > > Eg, the category 1 (billing) data for employee 1 would have a range
> > > > > something like:
> > > > > d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> > > > > category 2 is one column over and would be...
> > > > > e6;e22;e46;i6;i22 etc.
> > > > >
> > > > > and so on for each category for each of 14 or so employees.
> > > > >
> > > > > To make this more difficult, because employees are constantly changing,
> > > > > there is no clear pattern to the rows that belong to each employee.
> > > > >
> > > > > Is there an easy way for me to sum these ranges without having to click 
> > > > > on
> > > > > each and every cell holding down CTRL?  If I do this, and someone 
> > > > > deletes
> > > > a
> > > > > row - it would wreck the totals, wouldn't it?
> > > > >
> > > > > If I could have a list of the row numbers for each employee, and the
> > > > column
> > > > > letters, and could combine these into cell references that would be 
> > > > > great,
> > > > > but I'm not sure if that is possible.
> > > > >
> > > > > Does anyone have any suggestions how I could do this?
> > > > >
> > > > > Cheers,
> > > > > Caroline (COE)
> > > > >
> > > > 
> > > > 
> > > >
> 

Hi

I have been looking at the array formula, but I'm not familiar with some of 
the functions.  Could you help me understand which part of the formula tells 
Excel which columns to use, and/or how it uses the different categories?  If 
I know that then I'm sure the rest will fall into place.

Thank you very much

COE

"COE" wrote:

> Thanks Domenic,
> 
> I will have to try this out.  (Catering for the data being on a different 
> sheet of the workbook to the calculation).
> 
> Cheers
> COE
> 
> "Domenic" wrote:
> 
> > Assuming that D6:S100 contains your data...
> > 
> > 1) Specify which rows to sum...
> > 
> > A1:
> > 
> > =CELL("row",D6)
> > 
> > A2:
> > 
> > =CELL("row",D22)
> > 
> > A3:
> > 
> > =CELL("row",D46)
> > 
> > Here, Rows 6, 22, and 46 will be summed.
> > 
> > 2) Specify which category to sum...
> > 
> > B1:  1
> > 
> > 1 equals Category 1 (Columns D, H, L, and P)
> > 
> > 2 equals Category 2 (Columns E, I, M, and Q)
> > 
> > 3 equals Category 3 (Columns F, J, N, and R)
> > 
> > 4 equals Category 4 (Columns G, K, O, and S)
> > 
> > 3) Then, try the following formula which needs to be confirmed with 
> > CONTROL+SHIFT+ENTER, not just ENTER...
> > 
> > =SUM(IF(ISNUMBER(MATCH(ROW(D6:S100),A1:A3,0)),IF(MOD(COLUMN(D6:S100)-COLU
> > MN(D6),4)+1=B1,D6:S100)))
> > 
> > Note that it allows you to delete rows.
> > 
> > Hope this helps!
> > 
> > In article <BDEBCA08-DB7A-423D-B27A-3ABD761B1552@microsoft.com>,
> >  COE <COE@discussions.microsoft.com> wrote:
> > 
> > > Well, for the time being I have decided to name all the ranges, using the 
> > > Autofilter to make it easier to select all of the correct cells.
> > > 
> > > Maybe there is an easier way, but I guess it will be via VBA and I can't 
> > > guarantee that there will be anyone in the office who will know even very 
> > > simple VBA in the future.
> > > 
> > > So to keep it simple to maintain, I guess I'll have to put up with lots of 
> > > named ranges.
> > > 
> > > COE
> > > 
> > > "COE" wrote:
> > > 
> > > > Hi Bob,
> > > > 
> > > > Each employee has between 1-3 rows of information, but there isn't a 
> > > > pattern 
> > > > to these row numbers that covers all employees.
> > > > 
> > > > If I could just enter this row information in some cells, and the columns 
> > > > into another cell and combine these into cell references that I could use, 
> > > > then I would be rolling, but I don't know if that is possible.
> > > > 
> > > > eg. Employee 1 has rows 6, 22, 46.
> > > > Employee 2 - 7, 23, 47
> > > > Employee 3 - 11
> > > > Employee 4 - 12, 28, 52
> > > > 
> > > > Somehow I'm getting a feeling there is no easy way to do this,
> > > > COE
> > > > "Bob Phillips" wrote:
> > > > 
> > > > > It is difficult to imagine that this is possible if there is no pattern. 
> > > > > Is
> > > > > there not any details in other cells that might indicate which cells to 
> > > > > SUM?
> > > > > 
> > > > > -- 
> > > > >  HTH
> > > > > 
> > > > > Bob Phillips
> > > > > 
> > > > > (remove nothere from email address if mailing direct)
> > > > > 
> > > > > "COE" <COE@discussions.microsoft.com> wrote in message
> > > > > news:47632B5B-4992-4B90-90F1-C97AA0ABC579@microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I am having trouble trying to work out how to sum some non-contiguous
> > > > > ranges
> > > > > > in Excel 2000.
> > > > > >
> > > > > > The data is on sheet 1.  It is budget data for a number of employees.
> > > > > > There are 12 cells (one for each month), for four different categories,
> > > > > for
> > > > > > each employee.
> > > > > >
> > > > > > Eg, the category 1 (billing) data for employee 1 would have a range
> > > > > > something like:
> > > > > > d6;d22;d46;h6;622;h46;l6;l22;l46;p6;p22;p46
> > > > > > category 2 is one column over and would be...
> > > > > > e6;e22;e46;i6;i22 etc.
> > > > > >
> > > > > > and so on for each category for each of 14 or so employees.
> > > > > >
> > > > > > To make this more difficult, because employees are constantly changing,
> > > > > > there is no clear pattern to the rows that belong to each employee.
> > > > > >
> > > > > > Is there an easy way for me to sum these ranges without having to click 
> > > > > > on
> > > > > > each and every cell holding down CTRL?  If I do this, and someone 
> > > > > > deletes
> > > > > a
> > > > > > row - it would wreck the totals, wouldn't it?
> > > > > >
> > > > > > If I could have a list of the row numbers for each employee, and the
> > > > > column
> > > > > > letters, and could combine these into cell references that would be 
> > > > > > great,
> > > > > > but I'm not sure if that is possible.
> > > > > >
> > > > > > Does anyone have any suggestions how I could do this?
> > > > > >
> > > > > > Cheers,
> > > > > > Caroline (COE)
> > > > > >
> > > > > 
> > > > > 
> > > > >
> >