Index, return only values

[ilcaa]

hello i have a range that has formulas, and the cells either returns a value or isblank (but has a formula in the cell), the range spans 2500 rows and has about 450 values (from the formula)...

i just want to return the values. If i do isNumber(index(b2:b2500, 0) ) i get a bunch of FALSES and some TRUES (the values). how can i just return a list (array) of the TRUES (the actual values)

tahnks

 

[cmowla]

I'm not sure what version of Office you have (update your account details perhaps?), but it sounds like you have one which allows array formulas.

Does this work for you (and give you want you want)?

a.xlsx
	A	B	C	D
1		Data		1
2		1		3
3		A		0
4		3		111
5		0		33
6		0f
7		111
8		33
Sheet2

Cell Formulas
Range	Formula
D1:D5	D1	=FILTER(IF(ISNUMBER(B2:B2500),B2:B2500,""),IF(ISNUMBER(B2:B2500),B2:B2500,"")<>"")
Dynamic array formulas.

 

[ilcaa]

thanks, yes i have 365

 

[Fluff]

thanks, yes i have 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option is

=FILTER(B2:B500,ISNUMBER(B2:B500))

 

[ilcaa]

Another option is

=FILTER(B2:B500,ISNUMBER(B2:B500))

i want to count the # of values (numbers), i assumed i could use same logic in countif(), but Countif(b2:b2000, isnumber(b2:b2000)) I get a #Spill! error. i was expecting a scalar. what happened? whats best way? thanks

 

[Fluff]

How about

=COUNT(FILTER(B2:B500,ISNUMBER(B2:B500)))