Count each occurance in range

Hi,

I have a column with values and empty cells.
1-6
2-9
3-
4-2
5-3

Now I want to count how many rows have a value.
in this case it would be 4 as row 3 is empty.
i just cant get the formula right...

i tried if(countif(b4:b301;isempty(b4:301;1;0)) and variantions but it didnt work...

anyidea ?

Originally Posted by godspeed

Hi,

I have a column with values and empty cells.
1-6
2-9
3-
4-2
5-3

Now I want to count how many rows have a value.
in this case it would be 4 as row 3 is empty.
i just cant get the formula right...

i tried if(countif(b4:b301;isempty(b4:301;1;0)) and variantions but it didnt work...

anyidea ?

simple COUNT function should work for you
try this
=COUNT(A1:A5)
it will return 4 in your case.

Regards

Tried that, it gives me "name?"

I have formulas in the empty cells, I guess they are not empty then.... but I just the the amount of 6s,7s in that column..

best regards
godspeed

no its not working either..

can you put a sample workbook for consideration.

you can not fetch values from other book when that book is closed.

>0 means values which are greater than 0. it will count only numbers which are greater than 0 (ignoring text)
<>0 means that values which are not equal to 0. putting <>0 in countif function will give number of cells which do not contain 0 but anyother value (it might include text)

this might help you understand (let me know)
Regards

well if I do

='C:\DOKUME~1\ADMINI~1\LOKALE~1\Temp\Rar$DI00.969\[Test1.xls]Sheet1'!$A$2

it gets the avalues if the other book is closed

if I do
=countif('C:\DOKUME~1\ADMINI~1\LOKALE~1\Temp\Rar$DI00.969\[Test1.xls]Sheet1'!$A$2;2)

it doesnt. whats the difference ?

does <>0 also count the cells that contain formulas but no value ? I only have 117 of those, with only 84 containing a formula but no value.
this still doesnt add up to 297...

Excel functions cannot fetch data from other workbooks when other workbooks are closed.

countif do not include cell where functions return blank cell or 0