COUNTIF function - greater than/smaller than ranges

  • Thread starter Thread starter El
  • Start date Start date
E

El

I am trying to create a formula that will count the number
of entries in a range, sorted by the following criteria:

<140,000
140000-180000
180001-250000
250000
Click to expand...

I have managed to use the COUNTIF function to represent
the first and last columns, but I'm not sure how to enter
the formula so that it can pick up values between the
ranges. Any ideas?

Thanks
Elle
 
Elle

Here are a couple of ways. Assume that the raw data is in
the range B9 - B15

1)
=COUNTIF(B9:B15,"<"&140000)
=COUNTIF(B9:B15,">="&140000)-COUNTIF(B9:B15,">"&180000)
=COUNTIF(B9:B15,">="&180001)-COUNTIF(B9:B15,">"&250000)
=COUNTIF(B9:B15,">"&250000)

2)
=SUMPRODUCT((B9:B15<140000)*1)
=SUMPRODUCT((B9:B15>=140000)*(B9:B15<=180000))
=SUMPRODUCT((B9:B15>180000)*(B9:B15<=250000))
=SUMPRODUCT((B9:B15>250000)*1)


Tony
 
Back
Top