Countif function

duewaynec

Board Regular
Joined
Jan 21, 2005
Messages
65
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

BJK

New Member
Joined
Feb 12, 2004
Messages
15
I'm not completely sure what you're asking for,
will this help?

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

duewaynec

Board Regular
Joined
Jan 21, 2005
Messages
65
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
Joined
Oct 13, 2003
Messages
144

ADVERTISEMENT

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
Joined
Jan 21, 2005
Messages
65

ADVERTISEMENT

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
Joined
Oct 13, 2003
Messages
144
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.
 

howzat

Board Regular
Joined
Oct 13, 2003
Messages
144
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}.
 

Forum statistics

Threads
1,147,680
Messages
5,742,590
Members
423,740
Latest member
JCF_

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?

Disable AdBlock

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
Top