Weird problems in Excel, need help explanning this...

bomberman411

Board Regular
Joined
Oct 23, 2007
Messages
169
Hello, I have some weird problems that I don't know how to fix (or even explain). Mostly, my code is good and works well in all my spreadsheets but I find myself trying to debug stuff that should be working, but isn't working 100% as it should.

For example, I have a cell that counts information using
Code:
=NB($A$8:$A$1000)
BTW: NB is COUNT in french, just so you know...

The weird problem is, it seems to skip the first 10-15-20 rows; it doesn't count them. So the total in the cell should be around 150, but it's 129?...
Can someone give me an alternative code to count cells?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
That looks like a spreadsheet formula rather than VB code.

Have you looked at COUNTA or COUNT?
Would =ROWS($A$8:$A$1000)*COLUMNS($A$8:$A$1000) work for you?
 
Upvote 0
Is it possible that the data in the first few rows is not truly numeric?

Do you get the correct result when using the equivalent of COUNTA?

Dom
 
Upvote 0
Would =ROWS($A$8:$A$1000)*COLUMNS($A$8:$A$1000) work for you?

It gives me a total of 993... so it's not working for me. I will try COUNTA, but COUNT is what I am using now (NB is french for COUNT), so I know that's not working right.
 
Upvote 0
If I'm using COUNT or COUNTA, the data absolutely has to be NUMERICAL? I can't just tell COUNT to count all the cells that have "something" in it? Then mayby I could count the blank cells and substract that from the total amount of cells... but that seems overly complicated for nothing... I just want it to count the cells that have something in it.
 
Upvote 0
My data isn't numerical because I need it to be "0001", "0002"... etc... so I put it as text just to have the peace of mind that it won't be "1" or "2"... you know what hapens when you put zeros in front of a number and put it in a numerical cell ... the zeros dissapear.
 
Upvote 0
My data isn't numerical because I need it to be "0001", "0002"... etc... so I put it as text just to have the peace of mind that it won't be "1" or "2"... you know what hapens when you put zeros in front of a number and put it in a numerical cell ... the zeros dissapear.

If NB (or COUNT) is counting some of the entries then it suggests that something is not consitent within your data. Are some entered as text as you describe and others maybe just formatted to appear that way?

Dom
 
Upvote 0
Actually, I just noticed that I have part of my data cells that have an ' in front of my number...

So in the cell, instead of having "0001", it's '0001 , but I can only see it when on select the cell and look in the function section up top.

Is that what you meant by the difference between data being formatted as text while others are "entered" as text Domski?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top