sum of "names"

[TAlex1st]

I have assigned values to several "names" by defining them via Insert/Names/Define.



I want to calculate the sum of a row of cells, where each cell will have have one of the "names" entered.

 

[Trebor76]

Hi TAlex1st,

Welcome to MrExcel!!

For named ranges called "Name1", "Name2" and "Name3" (change to suit), you'd simply put the following formula in whatever cell you wish to see the result:

=Name1+Name2+Name3

HTH

Robert

 

[TAlex1st]

Except the cells wont have a specific "name" in them. I want it to calculate no matter which combination of my defined "names" are entered into the fields.

 

[Trebor76]

Except the cells wont have a specific "name" in them

The cells can be summed by the name you have defined those cells by re my example

Using my example of named ranges 'Name1', Name2' and 'Name3', you could also use...

=SUM(Name1,Name2,Name3)

...to sum the values held within them.

HTH

Robert

 

[TAlex1st]

Lets say I'm working with the names: cat, bat, jar and dog. They have numerical values. I want to calculate 3 cells, no matter which 3 names are entered: cat, jar, dog or dog, bat, cat or jar, bat, cat.

 

[Trebor76]

Still not sure why you've mentioned the named ranges in your original post, but try this - note you'll have to change the amount each text string is allocated and the cell references where the text string reside:

=IF(ISERROR(INDEX({10,20,30,40},MATCH(A2,{"cat","bat","jar","dog"},0))+INDEX({10,20,30,40},MATCH(A3,{"cat","bat","jar","dog"},0))+INDEX({10,20,30,40},MATCH(A4,{"cat","bat","jar","dog"},0))),0,INDEX({10,20,30,40},MATCH(A2,{"cat","bat","jar","dog"},0))+INDEX({10,20,30,40},MATCH(A3,{"cat","bat","jar","dog"},0))+INDEX({10,20,30,40},MATCH(A4,{"cat","bat","jar","dog"},0)))

 

[Aladin Akyurek]

Lets say I'm working with the names: cat, bat, jar and dog. They have numerical values. I want to calculate 3 cells, no matter which 3 names are entered: cat, jar, dog or dog, bat, cat or jar, bat, cat.

Excel takes a string in a cell as a string. i.e., it does not attempt to evaluate it in order to see whether it's a name in the name space. But, although not generic, something like...

=SUM(EVAL(A2),EVAL(A3),EVAL(A4))

would do the job. EVAL is a UDF with the following code:

______________________________________________
Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
' Charles Williams aka FastExcel
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
______________________________________________