select column in defined group of rows

[Fiboeh]

Hello,
I have a really primary problem, where I need simple syntax for.
In my excel-sheet I have defined several groups of data, by defining different rows to a name. Group 1 = whole rows 2, 6, 9, ... ; Group 2 = rows 1, 3, 19, ... and so on.Now I want to do calculations with only numbers out of one column from a certain group.
How is this basic syntax done? I can't find it nowhere, but I think it must be possible.

So for example my group1 is =Sheet1!$43:$174;Blad1!$9:$22 and I want to use all the numbers in column Q from it.
What is the syntax to sum them? =sum(?)

Thanx in advance.

[Marcol]

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[Fiboeh]

Here is a sample of my file
Thx to help me.

[Marcol]

Hmm?

I'm not sure what you are trying to do, but I don't think using names on a non-continuous range is going to work.

L32:L33, L35:L36 shows the result when you try to sum a column within these names

Better you use another column to identify the group the record is in, that is "Group" in this example.
Now see the results in M32:M33, M35:M36

You might also consider using SUBTOTAL() and filters.

Filter "Group" for 1 or 2 see the result in L29:M29 and compare with M32:M33, M35:M36

[Fiboeh]

Thank you very much Marcol,
I just didn't know there was something like the index function.
And its very hard to explain what i mean, but it is just to use the self declared name and a row via the INDEX(group;;[selects which colomn).

[Hercules1946]

How do you determine the group that a record belongs in, and why can't you have separate sheets (or blocks within a sheet) for each group?