Count only cells where formula returns a number

[Rainmanne]

I've got a table where I need to count a number of cells that are numbers. The numbers are returned by formulas and it might be any number of years as well as text headings.

I tried something like

=SUMPRODUCT((1:1)*ISNUMBER(1:1))

But it does not work.

 

[hrayani]

Hello,

Try

=SUMPRODUCT(--ISNUMBER(1:1),1:1)

 

[Fluff]

How about

=count(A1:AZ1)

 

[Sanjeev1976]

Try

=COUNT(1:1)

 

[hrayani]

Hello,

Try

=SUMPRODUCT(--ISNUMBER(1:1),1:1)

My BAD!
it's returning sum - see formula from @Fluff in POST # 3

 

[Rainmanne]

How about

=count(A1:AZ1)

It returns 0. I think because the numbers are returned by fromulas.

 

[Fluff]

If it returns 0 then your numbers are text & not numbers. You need to change your formula so that it returns real numbers.

 

[Rainmanne]

If it returns 0 then your numbers are text & not numbers. You need to change your formula so that it returns real numbers.

Oh yes, it's true. Sorry. But if I convert them into real numbers (using NUMBERVALUE), I will have to change a lot of other formulas. I tried something like

=COUNT(NUMBERVALUE(A1:Z1))

But it gives me 23. Any way to convert them into numbers in the formula? It will save me a lot of trouble rewriting other formulas.

 

[Fluff]

Any way to convert them into numbers in the formula?

Not really, especially if you have cells that return "". At then end of the day numbers should always be real numbers & not text, otherwise it is likely to lead to all sorts of problems.

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		J	G		C	5	K		E	6		H	D	B
2
3	Count of Numbers:	2
4
Sheet2

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(ISNUMBER(--B1:N1)*(LEN(B1:N1)>0))