Calculation/Sum based on Name Box

[frohanss]

Hi,

scattered all around in my big excel sheet i have named some cells by name. And now i need a easy way to sum up the values in thos cells.

One of the cell name box values i have used "L11x361", "L11x362" and so on (just changing the last number).
I also need the sum to to calculate from "L11x361" to let's say "L11x3615" even tho i only have used up to "L11x369". This is just if i in the future add some new cells to this series.

If i just try to type "=L11x361+L11x362+L11x363+L11x364+L11x365+L11x366" (and so on up to value 15) It only shows "#NAME?" cause some of the name's hasn't been used!

Can anyone help me? Can this be done?

[David McRitchie]

=SUM(namedrange) don't put your named range(s) name within quotes
=SUM(B14, namerange1, namerange2)

The SUM Worksheet Function will ignore text cells and error cells.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"frohanss" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> scattered all around in my big excel sheet i have named some cells by
> name. And now i need a easy way to sum up the values in thos cells.
>
> One of the cell name box values i have used "L11x361", "L11x362" and so
> on (just changing the last number).
> I also need the sum to to calculate from "L11x361" to let's say
> "L11x3615" even tho i only have used up to "L11x369". This is just if i
> in the future add some new cells to this series.
>
> If i just try to type
> "=L11x361+L11x362+L11x363+L11x364+L11x365+L11x366" (and so on up to
> value 15) It only shows "#NAME?" cause some of the name's hasn't been
> used!
>
> Can anyone help me? Can this be done?
>
>
> --
> frohanss
> ------------------------------------------------------------------------
> frohanss's Profile: http://www.excelforum.com/member.php...o&userid=26917
> View this thread: http://www.excelforum.com/showthread...hreadid=401402
>

[frohanss]

Thx for your reply!

Can't seem to get it to work!
If i try:
=SUM(L11x361:L11x369) Shows= #NAME?
=SUM(L11x361;L11x369) Shows= #NAME?

And i haven't used name L11x369 in any namebox. So no cell is called that name.

Both of this work as longe as i use names that already is used by a cell.

Have i done anything wrong or is there a option in excel to bypass this?

[Dave Peterson]

This seems like it could turn into a design nightmare.

Have you thought of defining the names (maybe on a separate hidden worksheet??),
then just redefine the "refersto" box when you actually need it? (Or just
delete the name and reapply it to what you want.)

But if you want, you could use a formula like:

=SUM(IF(ISNUMBER(INDIRECT("L11x363")),L11x363,0),
IF(ISNUMBER(INDIRECT("L11x364")),L11x364,0),
IF(ISNUMBER(INDIRECT("L11x365")),L11x365,0))

All one cell

You don't actually need the ,0) portion. If the =isnumber() returns false, then
that won't add to the sum.

So this slightly smaller formula would work, too:

=SUM(IF(ISNUMBER(INDIRECT("L11x363")),L11x363),
IF(ISNUMBER(INDIRECT("L11x364")),L11x364),
IF(ISNUMBER(INDIRECT("L11x365")),L11x365))

Still all one cell.

frohanss wrote:
>
> Hi,
>
> scattered all around in my big excel sheet i have named some cells by
> name. And now i need a easy way to sum up the values in thos cells.
>
> One of the cell name box values i have used "L11x361", "L11x362" and so
> on (just changing the last number).
> I also need the sum to to calculate from "L11x361" to let's say
> "L11x3615" even tho i only have used up to "L11x369". This is just if i
> in the future add some new cells to this series.
>
> If i just try to type
> "=L11x361+L11x362+L11x363+L11x364+L11x365+L11x366" (and so on up to
> value 15) It only shows "#NAME?" cause some of the name's hasn't been
> used!
>
> Can anyone help me? Can this be done?
>
> --
> frohanss
> ------------------------------------------------------------------------
> frohanss's Profile: http://www.excelforum.com/member.php...o&userid=26917
> View this thread: http://www.excelforum.com/showthread...hreadid=401402

--

Dave Peterson

[frohanss]

Thanks!! IT WORKS!

Ok, the formula is big but that's no problem as long as it works!

I have about 120 different nameseries an a total of about 1200 named cells on different places in a total of 48 sheets. So the longe formula is more easy and safer than me picking out all the cells manualy!

Now i can have a happy weekend!

Thanks one more time David! Your a life saver!
(At least a weekend saver)