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

#### bomberman411

##### Board Regular
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### mikerickson

##### MrExcel MVP
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?

#### Domski

##### Well-known Member
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

#### bomberman411

##### Board Regular
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.

#### bomberman411

##### Board Regular
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.

#### bomberman411

##### Board Regular
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.

#### Domski

##### Well-known Member
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

#### bomberman411

##### Board Regular
What would the equivalent be using COUNTIF?

COUNTIF(\$A\$8:\$A\$1000; notempty)?

#### bomberman411

##### Board Regular
What is the difference between data being formatted as text while others are "entered" as text?....?

#### bomberman411

##### Board Regular
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?

Replies
10
Views
973
Replies
0
Views
302
Replies
5
Views
722
Replies
0
Views
212
Replies
2
Views
374

1,191,691
Messages
5,988,111
Members
440,126
Latest member
duque00

### 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?

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