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(M OD(COLUMN(D6:S100)-COLU
MN(D6),4)+1=B1,D6:S100)))

Note that it allows you to delete rows.

Hope this helps!

In article ,
COE 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" wrote in message
...
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)