Countif function

[duewaynec]

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.

 

[BJK]

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

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

 

[Aladin Akyurek]

=COUNTIF(Range,">=790000000")-COUNTIF(Range,">=800000000")

 

[duewaynec]

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]

Hi,

Try this:

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

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

 

[duewaynec]

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]

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]

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

 

[howzat]

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.

=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}.