adding cell names

[sadath]

I have name some random names as name1, name2.... name5

to add these cells, the formula is not working

=SUMPRODUCT(INDIRECT("name"&{"1","2","3","4","5"}))

 

[sadath]

adding cell name

I have named some random cells as name1, name2.... name5

to add these cells, the formula is not working

=SUMPRODUCT(INDIRECT("name"&{"1","2","3","4","5"}))

 

[Richard Schollar]

Hi Sadath

I can't seem to get this to work either. Maybe someone else will have an idea how to. Do you need to have different names for all these cells - or could you just call all of them "Name1" say (ie select all the relevant cells and then go Insert>Name>Define and give them a name)?

 

[Jon von der Heyden]

How are the defined ranges defined? Perhaps this may shed some light: http://www.mrexcel.com/board2/viewtopic.php?t=189749&highlight=indirect

 

[Richard Schollar]

Hmm, following my own link in the link provided by Jon, it seems that Domenic (MrExcel formula wizard) has also had problems with a similar situation.

Fascinating...

 

[Krishnakumar]

Somethink like this

=SUMPRODUCT(SUMIF(INDIRECT("name"&ROW(INDIRECT("1:5"))),{">0","<0"}))

should work.

 

[GlennUK]

How about:

=SUMPRODUCT(N(INDIRECT("name"&{"1","2","3","4","5"})))

 

[Richard Schollar]

I still get a #REF with that formula Kris

 

[Krishnakumar]

So this..

=SUM(N(INDIRECT("name"&{"1","2","3","4","5"})))

 

[Richard Schollar]

Of course, it does help to actually have a cell named "Name1" when running the formula. Worked like a charm Kris

EDIT: Glenn - your formula was a thing of beauty too