# Countif function

#### duewaynec

##### Board Regular
hi,

i need help with the countif function. i need to count the number of times a value appears in a range. the value must be between 790000000 upto 7909z9999. where 'z' replaces the digit 9 when reached. is this possible???????

your help would be appreciated.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### BJK

##### New Member
I'm not completely sure what you're asking for,
will this help?

="7909"&countif(range;criteria)&"9999"

##### MrExcel MVP
=COUNTIF(Range,">=790000000")-COUNTIF(Range,">=800000000")

#### duewaynec

##### Board Regular
hi,

i have a sheet called 'realtimedata' which pulls data from one of 31 sheets in the same workbook. the other sheets are named '01' through to '31'.
the range that needs to be checked is G4:G183. the same range for all sheets 1 to 31. every sheet has its on cell on the 'realtimedata' sheet where the countif value needs to be inserted. so the formula will remain the same except for the ref to the different sheet.
i need a formula to check range G4:G183 on sheet 1 for any numbers between 79000000 to 7909z9999. if any of the cells in the range has a number between those values it must add 1.
does that make sense?

#### howzat

##### Board Regular
Hi,

Try this:

Code:
=INDEX(FREQUENCY('01'!G4:G138,{79000000;790999998}),2)+SUMPRODUCT(--('01'!G4:G138="7909z9999"))

This presuming your data in range G4:G138 goes from 79000000 (not including) to 790999999 (including), but all 790999999 are replaced by "7909z9999". if you dont want to include the 7909z9999, then leave the "+SUMPRODUCT(--('1'!G4:G138="7909z9999"))" part off the formula.

Hope this helps.

#### duewaynec

##### Board Regular
thanks.
i'll try it later.
was kind of stuck on this one

#### duewaynec

##### Board Regular
hi,

thanks for the help so far. it picks up any numbers between the given criteria except if the 79number includes a letter other than z. the things is, once the 5th digit reaches 9 it starts with a, once the 6to9th digit reaches 9999, the 7902a9999 rolls over to b so it would be 7902b0000 and so on. so it could be 7902c1234, or 7904f1234. or any value between 79000000 - 790999999.

#### howzat

##### Board Regular
This is assuming the text character will always be the 5th character in the string:

=INDEX(FREQUENCY(--(LEFT('01'!\$G\$4:\$G\$138,4)&9&MID('01'!\$G\$4:\$G\$138,6,6)),{79000000;790999998}),2)-SUMPRODUCT(--('01'!\$G\$4:\$G\$138=79000000))

Returns the number between, not including, 79000000 and 790999999.

If the position of the text character varies, a formula approach can be used, but may be cumbersome for 30 sheets.

#### duewaynec

##### Board Regular
you did it.
thanks sean it works perfectly now.
thanks for all the help.

#### howzat

##### Board Regular
Mmmmm.

I had another look at Aladin's formula, and although I'm not sure why it does not work, I think he was on the right track.

Here's a derivative of that formula that is far more simple. There are however limitations to this method in that it treats numbers as text and therefore disgards the length of the number, so "7906" would rank ahead of "790000000" and would be included in the calculation.

Code:
=SUMPRODUCT(--(G4:G138&"">"790000000"),--(G4:G138&""<"7909z9999"))

Also please note that my previous formula included {79000000;790999998}, which I think should have been {790000000;790999998}.

Replies
1
Views
61
Replies
1
Views
96
Replies
1
Views
328
Replies
4
Views
77
Replies
1
Views
89

1,181,994
Messages
5,933,162
Members
436,883
Latest member
RyanI1986

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