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"}))

 

[Krishnakumar]

I still get a #REF with that formula Kris

Book1
	A	B	C	D	E
1	6308	4127	730	9883	5162
2	text	8629		7135	7966
3	9754	2441		6252	5436
4		1903		440
5		3443		4848
6				6069
7
8		26210
9		26210			90526
10		90526
11
12	name1	=Sheet1!$A$1:$A$3
13	name2	=Sheet1!$B$1:$B$5
14	name3	=Sheet1!$C$1
15	name4	=Sheet1!$D$1:$D$6
16	name5	=Sheet1!$E$1:$E$3
Sheet1

Formulas:

B8:

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

B9:

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

B10:

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

E9:

=SUM(A1:E6)

If the range size are different then Glenn's formula fails.

It only sums the first cell of each range.

 

[sadath]

Dear Kris, solved with your formula, thanks