Calculation/Sum based on Name Box

F

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? :confused:
 
D

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.
 
D

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.
 
F

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?
 
F

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;))
 
Top