# Countif 4 digit Number starts with

#### blossomthe2nd

##### Active Member
Hi guys

I am trying to write a query that counts the number of 4 digit numbers that start with 3,

I have the text verson

=COUNTIF(A2:A7,"3???")

But this only finds 4 digits that start with 3 if it contains text ? so it will find 3ABC but not 3456 !

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
TRY:
=IF(LEFT(A2:A7,1)="3",COUNT(A2:A7),"")
insert with CTRL + SHIFT + RETURN

I would do it like this...

Excel 2003 and earlier:
Code:
``=COUNTIF(A2:A7,">=3000")-COUNTIF(A2:A7,">=4000")``

Excel 2007 and later:
Code:
``=COUNTIFS(A2:A7,">=3000",A2:A7,"<4000")``

Thanks Guys

Do you know if there is a way to just count Whole numbers ?

Or is there a way on formating to round DOWN a decimal ?

Thanks

Do you know if there is a way to just count Whole numbers ?
Try either of these
Code:
``````=SUMPRODUCT(--(A2:A7>=3000),--(A2:A7<=4000),--(INT(A2:A7)=A2:A7))

=SUMPRODUCT(--(A2:A7>=3000),--(A2:A7<=4000),--(LEN(A2:A7)=4  ))``````

Or is there a way on formating to round DOWN a decimal ?
Why do you want to round down... do you plan to sum the numbers as well?

Last edited:
Hi Macho,

Its grand I put a second column in with

=ROUNDDOWN(A2,0) and it works perfect

Replies
9
Views
373
Replies
8
Views
182
Replies
5
Views
837
Replies
4
Views
302
Replies
4
Views
412

1,219,569
Messages
6,149,040
Members
450,853
Latest member
xtiinctt

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