Excel 2007 : Counting number of events between several numeric ranges

**Mogpot1** · 07-04-2012, 07:06 AM

Good morning, I have been trying to resolve a problem with counting events between several numeric ranges. I have a single column that has numeric data in each field that could be anything from 0 to 500. I am trying to establish how many fields have numeric data between 0-1, 2-5, 6-10, 11-20, 21-30, 31-50 and 50 plus. There could be any number of repeats of the same number.

I have experimented with several formula from the forums using countif, countifs, sumproduct etc but none of them report the true number of events. For example within the column of data I have 13 fields with data between 21 and 30 but whatever formula I use it keeps returning 11 and this type of error is the same for most of the ranges I am trying to count. I am not sure if this is relevant but within each range I could have a single number that repeats itself 20,30 or 100 times.

Any help would be appreciated.

Trevor

**zbor** · 07-04-2012, 07:11 AM

Did you try COUNTIFS formula:

=COUNTIFS(A1:A1000, ">=1", A1:A1000, "<10")

And so for all values.

You can also write these values as references and use them:

=COUNTIFS(A1:A1000, ">="&C1, A1:A1000, "<"&D1)

**Mogpot1** · 07-04-2012, 08:12 AM

Thanks for your reply it is much appreciated. I have already used the formula you advise but it fails to return the correct result based on a manual count. It does not error but is simply not accurate. I have even copied the column of numbers to a separate spreadsheet and still get inaccurate results. Almost as if Excell can't count.

**zbor** · 07-04-2012, 08:23 AM

Can you upload example workbook?

**Mogpot1** · 07-04-2012, 08:39 AM

Thanks again, I can upload the file but just made one observation that might influence the results that perhaps you can advise on. I notice I have the visible column formatted to zero decimal places but the underlying data has up to 13 decimal places so a 20 could be 20.37629376 for example. Could this be the problem if so how do I force the underlying data to zero decimal places. I will upload the file to you if my observation is not relevant.

Thanks again.

**zbor** · 07-04-2012, 08:45 AM

Doesn't matter since >20 doesn't look only whole numbers (21,22,23...) but any number greater than 20 (20.73,21.58,20.000000000000000001)

But it won't count 19.9999999999999999 that it's visible as 20. That might be a problem.

**Mogpot1** · 07-04-2012, 09:31 AM

Zbor,

Thanks for your help. Your last reference did the trick. I have used your recommended formula COUNTIFS and for example instead of putting in 2 and 5 into the formula to count data between 2 and 5 I have entered 1.5 and 5.5 into the formula and it works. I tried it for all the other ranges and it worked perfectly. It seems the underlying data with extended decimal points was the problem. Thanks for the quick response and help.

Take care

Trevor