count if cells contains a number

**hluk** · 04-02-2012, 03:24 PM

hello!

found this thread and I want to do the same but using the countif function...

http://www.excelforum.com/excel-prog...-a-number.html

is there a way to adapt the non-vba if statement and place into the below code?

=COUNTIF(my_data!X:X, [what to put here?] )

Thank you so much!

**Søren Larsen** · 04-02-2012, 03:49 PM

So, you want a non-VBA solution? In that case:

I don't if you can use =COUNTIF(), but the following should work:

=SUMPRODUCT((ISNUM(X:X)*1)

**daddylonglegs** · 04-02-2012, 04:13 PM

I'm assuming you want a count of cells in a range which contain any digits, If so try this

=SUMPRODUCT((MMULT(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},Your_range))+0,{1;1;1;1;1;1;1;1;1;1})>0)+0)

**hluk** · 04-02-2012, 05:50 PM

Originally Posted by daddylonglegs

I'm assuming you want a count of cells in a range which contain any digits, If so try this

=SUMPRODUCT((MMULT(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},Your_range))+0,{1;1;1;1;1;1;1;1;1;1})>0)+0)

works perfectly. +1 rep.