Count if function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a count of items (numbers) that fall in a range. Exactly, I have a list of 2,000 plus numbers. I want to count those that are greater than one number but less than or equal to another number. Seems simple, but I haven't happened upon the right expression yet. I have been trying it with a smaller subset of points to test it. No luck yet. Help!!
 
One way

=COUNTIF(A2:A2002,">2")-COUNTIF(A2:A2002,">50")

Another way

=SUMPRODUCT(--(A2:A2002>2),--(A2:A2002<=50))

--

Regards,

Peo Sjoblom


hookedonexcel said:
I am trying to get a count of items (numbers) that fall in a range.
Click to expand...
Exactly, I have a list of 2,000 plus numbers. I want to count those that
are greater than one number but less than or equal to another number. Seems
simple, but I haven't happened upon the right expression yet. I have been
trying it with a smaller subset of points to test it. No luck yet. Help!!
 
=COUNTIF(A1:A100,"<=10000)-COUNTIF(A1:A100,<=5000)

will count the numbers in the range that are greater than 5,000 and less
than or equal to 10,000.

--

Vasant

hookedonexcel said:
I am trying to get a count of items (numbers) that fall in a range.
Click to expand...
Exactly, I have a list of 2,000 plus numbers. I want to count those that
are greater than one number but less than or equal to another number. Seems
simple, but I haven't happened upon the right expression yet. I have been
trying it with a smaller subset of points to test it. No luck yet. Help!!
 
Hookedonexcel,

First count the range greater than your number

=COUNTIF(A1:A2000, ">100")

Then count the ones greater than your second number

=COUNTIF(A1:A2000, ">1000")

Subtract

=COUNTIF(A1:A2000, ">100") - COUNTIF(A1:A2000, ">1000")

Gives 100 < A1:2000 <= 1000

If you want the range 100 <= X <= 1000 use:
=COUNTIF(A1:A2000,">=100") - COUNTIF(A1:A2000,">1000")
OR
=SUMPRODUCT((A1:A2000>=100)*(A1:A2000<=1000))
includes 100 and 1000

for 100 < X < 1000 use:
=COUNTIF(A1:A2000,">100") - COUNTIF(A1:A2000,">=1000")
OR
=SUMPRODUCT((A1:A2000>100)*(A1:A2000<1000))
excludes 100 and 1000

for 100 <= X < 1000
=COUNTIF(A1:A2000,">=100") - COUNTIF(A1:A2000,">=1000")
OR
=SUMPRODUCT((A1:A2000>=100)*(A1:A2000<1000))

for 100 < X <= 1000
=COUNTIF(A1:A2000,">100") - COUNTIF(A1:A2000,">1000")
OR
=SUMPRODUCT((A1:A2000>100)*(A1:A2000<=1000))

Dan E

hookedonexcel said:
I am trying to get a count of items (numbers) that fall in a range. Exactly, I have a list of 2,000 plus numbers. I want to
Click to expand...
count those that are greater than one number but less than or equal to another number. Seems simple, but I haven't happened upon
the right expression yet. I have been trying it with a smaller subset of points to test it. No luck yet. Help!!
 
Thanks everyone! I should have been able to come up with one of those. They all work. I love this discussion board.
 
Back
Top