Formula for Non-contiguous ranges

[LearnMeExcel]

HI all
i have this data

Excel Workbook
	A	B	C	D	E
1	Area 1	15
2	Area 2		#DIV/0!
3	Area 3				6
4	Area 4		6
5	Area 5	10		#N/A
6	Area 6				5
7
8
9	Excel 2010	42
10	CSE	#VALUE!
11	Sumproduct	#VALUE!
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B9	=AGGREGATE(9,6,B1,C2,E3,C4,B5,D5,E6)
B11	=SUMPRODUCT(--ISNUMBER((B1,C2,E3,C4,B5,D5,E6)),(B1,C2,E3,C4,B5,D5,E6))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

in CSE and sumproduct it gives error
how i can solve it

 

[j_latendre]

Try this in B10:

=IF(ISERROR(OR(B1,C2,E3,C4,B5,D5,E6)),"",SUM(B1,C2,E3,C4,B5,D5,E6))

Try this in B11:

=SUMPRODUCT(--ISNUMBER(OR(B1,C2,E3,C4,B5,D5,E6)),(B1,C2,E3,C4,B5,D5,E6))

Im unsure if these will help but worth a try :S

Jesse

 

[DonkeyOte]

SUMPRODUCT not really worth pursuing IMO.

Arrays - to work pre XL2007 etc:

=SUM(IF(ISNUMBER(CHOOSE({1,2,3,4,5,6,7},B1,C2,E3,C4,B5,D5,E6)),CHOOSE({1,2,3,4,5,6,7},B1,C2,E3,C4,B5,D5,E6)))
confirmed with CTRL + SHIFT + ENTER

or:

=SUM(IF(ISNUMBER(N(INDIRECT({"B1","C2","E3","C4","B5","D5","E6"}))),N(INDIRECT({"B1","C2","E3","C4","B5","D5","E6"}))))
confirmed with CTRL + SHIFT + ENTER

 

[LearnMeExcel]

thanx Lucke
it is great formula
but i think it is not easy to write it
special if i have more 20 cells Non-contiguous
i should write every cell alone
another point please
when i evaluate the second formula
=SUM(IF(ISNUMBER(N(INDIRECT({"B1","C2","E3","C4","B5","D5","E6"}))),N(INDIRECT({"B1","C2","E3","C4","B5","D5","E6"}))))
at the first step of evaluate this part
INDIRECT({"B1","C2","E3","C4","B5","D5","E6"})
apears as error
N({#VALUE,#VALUE,#VALUE,#VALUE,#VALUE,#VALUE,#VALUE})
then after evaluate n the correct result apear
why ??

 

[DonkeyOte]

A formula of this nature isn't going to be straightforward I'm afraid...

There are some functions that work happily with non-contiguous ranges (SUM, LARGE etc...) but not so well with underlying errors.

Other functions work happily with underlying errors but not so well with non-contiguous ranges (SUMIF for ex.)

Regards your query concerning use of Evaluate Formula, #VALUE! and N... I'm afraid I don't have an answer for you there - all I know is that you need to use N to get the number (T for text equivalent).