# Countif() giving different values ?

#### Skydyno

##### New Member
Hi All

Row 6:6 contains a mixture of cells containing text and formula that return numbers. I wish to count the either the number of numbers or the number of non empty cells.

The number of non blank cells, either with text(or a formula returning text is constant at 3) or a number (varies) is 7. Therefore the number of rows containing a number atm is 4

What I don't understand is why I'm getting diferent results...

In the worksheet cell formula =COUNTIF(6:6,">0") - this returns 4

When I try to use VBA to do the same thing I get a different answer...

Code:
``Check_1 = Application.WorksheetFunction.CountIf(Sheets(Which_Sheet).Range("6:6"), ">0")``

this returns 1

As does

Code:
``Check_2 = Worksheets(Which_Sheet).Range("6:6").Cells.SpecialCells(xlCellTypeConstants).Count``

Is it to do with Rows? When I've used these in columns before they have returned the correct answers..

Sorry if I'm just being stupid...

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Can't see why:

 A​ B​ C​ D​ E​ F​ G​ H​ 1​ a​ b​ c​ 1​ 2​ 3​ 4​ 2​ 3​ 4​ 4​ =COUNTIF(1:1,">0")​ 5​ 6​ 7​ ?application.WorksheetFunction.CountIf(Rows("1:1"),">0")​ 8​ 4​ 9​ ?application.WorksheetFunction.CountIf(Rows(1),">0")​ 10​ 4​ 11​ ?application.WorksheetFunction.CountIf(Range("A1").EntireRow,">0")​ 12​ 4​ 13​ ?application.WorksheetFunction.CountIf(Range("A1").EntireRow.SpecialCells(xlCellTypeConstants),">0")​ 14​ 4​ 15​ ?application.WorksheetFunction.CountIf(Range("1:1"),">0")​ 16​ 4​ 17​ ?application.WorksheetFunction.CountIf(Range("1:1").SpecialCells(xlCellTypeConstants),">0")​ 18​ 4​ 19​ ?application.WorksheetFunction.Count(Range("1:1"))​ 20​ 4​ 21​ ?application.WorksheetFunction.CountA(Range("1:1"))​ 22​ 7​

<tbody>
</tbody>

Are you sure Which_Sheet is the correct sheet name?

Hi

Maybe you can prepare a small example?

Remark:

Your second statement is not equivalent to the first one, The CountIf() counts number vales bigger than zero, while the .SpecialCells that you posted will count any type of constants.

Well, as it turns out.... The answer was.... Yes, I'm stupid

Thanks for the replies. @TMShucks thanks, helped and simplified some other challenges.

Are you sure Which_Sheet is the correct sheet name?

That, as it turns out, was the problem. As you replied I figured out that the SHEETS("Name of sheet").RANGE.... (in my case Which_Sheet)... takes the left most sheettab in the workbook as 1 (Default name of Sheet1).

err..ok

So when you change it's name, the reference still remains 1. My workbook references different sheets based on a vlookup to return a number and goto that sheet.

Sheet1 and Sheet2 have been renamed. The rest of the sheets are renamed sequencially, 1,2,3... etc. So I've ended up with Sheets displaying one number and having a slightly different reference number.

In my code, Which_Sheet = 3 so SHEETS(WHICH_SHEET).RANGE... was compiled to SHEETS(3).RANGE... and SHEET(3) is the third one along which is infact labelled "1"

... so pulling the information from the wrong sheet. If you follow me...

DOH.

With this is mind how do I reference sheet in VBA using thier visible tab name ?

RESOLVED.

Thanks to everyone for thier time and help...

With this is mind how do I reference sheet in VBA using thier visible tab name ?

Code:
``Sheets("1")``
as opposed to
Code:
``Sheets(1)``

Code:
``Sheets("1")``
as opposed to
Code:
``Sheets(1)``

Again thanks....

Replies
6
Views
386
Replies
5
Views
225
Replies
2
Views
299
Replies
1
Views
520
Replies
3
Views
266

1,196,048
Messages
6,013,088
Members
441,747
Latest member
darkman77

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

### Which adblocker are you using?

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

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