Finding Results between two numbers

M

mark

Scenario: I have 120+ values in a spreadsheet. What I
need to know is how to write a COUNT function that will
count the number of entries betwee two different numbers
(e.g. between 200 and 600). I can get the countif for
less than 200, and one that is greater than 600, but not
between.

TIA
 
G

Graham Yetton

I'm sure there is a more elegant way but this works...
=COUNTIF(A1:A12,">2")+COUNTIF(A1:A12,"<6")-COUNT(A1:A12)
The numbers betwee 2 and 6 are counted twice so taking
away the total numbers leaves a count of those between the
two values.
HTH
Graham Yetton
 
B

Bob Phillips

Mark,

With what you can get, why not just count all entries and subtract the two
values you have?

Or another way, assuming in A1:A120

=SUMPRODUCT((A1:A120>=200)*(A1:A120<=600))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNTIFS (the numbers in a column, when subtracted meet a certain criteria) 1
Excel Countif Function - Calculating Between Two Sets of Values 3
Finding the proper style field and the numbered paragraph value within the style 0
Finding the right style field in VBA 0
Yes or No Value if Dollar amount between two numbers 1
Use TODAY() function as the criteria in a SUMIF formula 1
Excel TVM functions of RATE, NPER, PMT, PV, and FV fail to produceresults that are known about Geome 0
Excel - count if a number falls within an array in a column & add 2

Top