Excel 97 question - Countif formula

[Crewman47]

I've got a a spreadsheet with a column that contains numbers and I'm trying to, without any luck, find a way to count all the numbers that fall between specific ranges, eg 1-1000, 1001-2000 etc. I've tried looking but I can't get the search criteria to find anything that matches what I'm looking for so does anyone know how I can do this?

Cheers

 

[Michael Chris]

Assuming I'm understanding what you want to do, this should work.

A1:A100 being your column...

For 1-1000:

=COUNTIF(A1:A100,">=1")-COUNTIF(A1:A100,">1000")

And for 1001-2000:

=COUNTIF(A1:A100,">=1001")-COUNTIF(A1:A100,">2000")

That is if you want it as strict as your comments. If you have decimals you're going to want to change it a bit.

Example with decimals:

=COUNTIF(A1:A100,">0")-COUNTIF(A1:A100,">1000")

=COUNTIF(A1:A100,">1000")-COUNTIF(A1:A100,">2000")

 

[Crewman47]

That was one of the formulas I tried initially but originally didn't work. I just found out that for it to work properly, for 1-1000 for example, you put 1 in one cell and 1000 in the cell below it. I forgot about the horizontal/vertical referencing thing that formulae use.

Thanks for the help.

 

[Michael Chris]

Glad to hear that you got it working!