Adding Non-Contiguous Ranges

[Guest]

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)

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Domenic]

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!

 

[Guest]

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

 

[Guest]

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

 

[Domenic]

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

This part...

A1:

=CELL("row",D6)

A2:

=CELL("row",D22)

A3:

=CELL("row",D46)

....allows you to choose the rows you want summed. So, in this example,
rows 6, 22, and 46 are summed. If, for example, you want row 30 summed
instead of 22, change D22 to D30. Setting it up this way will allow you
to insert, add, or delete rows without throwing off the SUM(IF()
formula. If you wanted to add another row to sum, let's say row 50, you
would enter the following in another cell, let's say A4...

=CELL("row",D50)

....and you would change A1:A3 to A1:A4.

This part...

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)

....allows to you choose the categories. If you enter 1 in B1, Category
1 will be summed. If you enter 2, Category 2 will be summed, and so on.
As you can see, Category 1 consists of Columns D, H, L, and P. Category
2 consists of Columns E, I, M, and Q, and so on.

Does this help?

 

[Guest]

Hi Domenic, thanks for replying.

I understand the row part - that was fine.

But Excel help contains nothing about Categories so that is the part I'm
having problems with. So what exactly do you mean by category?

How does Excel know that Category 1 is columns D, H, L, and P?

(or maybe I'm just feeling a little dense this morning

Cheers,
COE

 

[Domenic]

This part of the formula...

MOD(COLUMN(D6:S100)-COLUMN(D6),4)+1=B1

....dictates which columns to sum. If you want, you could replace B1
with the relevant number. So, if you want to sum Columns D, H, L, and
P, you could have...

MOD(COLUMN(D6:S100)-COLUMN(D6),4)+1=1

In your initial post, you mentioned that you wanted to sum Columns D, H,
L, and P, which you referred to as Category 1. And then you want to sum
Columns E, I, M, and Q, which your referred to as Category 2, and so on.
Did I misunderstand your intention?

 

[Guest]

Aha. No, you're correct, Domenic & it works fine.

I was just having a little trouble getting my head around how it choose the
columns, that all. I do see how it calculates it now. Just wanted to be
sure so I can reproduce this when I need to use it in a different spreadsheet.

Many thanks.
COE

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!