Countif 4 digit Number starts with

[blossomthe2nd]

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 !

Can anyone help please ?

 

[machopicho]

TRY:
=IF(LEFT(A2:A7,1)="3",COUNT(A2:A7),"")
insert with CTRL + SHIFT + RETURN

 

[Colin Legg]

I would do it like this...

Excel 2003 and earlier:

=COUNTIF(A2:A7,">=3000")-COUNTIF(A2:A7,">=4000")

Excel 2007 and later:

=COUNTIFS(A2:A7,">=3000",A2:A7,"<4000")

 

[blossomthe2nd]

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

 

[Colin Legg]

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

Try either of these

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

 

[blossomthe2nd]

Hi Macho,

Its grand I put a second column in with

=ROUNDDOWN(A2,0) and it works perfect

Thanks for your help