Formaula to calculate "between"

K

Kimberly

Can anyone help me with writing a formula that will count how many enteries
fall between two numbers? Example: How would I find all cells that have a
value between 4 & 6 in the below scenario? The answer that I'm looking to
return is 1.

column A
1
5
8
9
 
B

BenjieLop

One way is

=countif(A1:A4,">4")-countif(A1:A4,">6")

NOTE: The ">6" argument is not a typo.

A second way is

=sumproduct(--(A1:A4>4),--(A1:A4<6))

Can anyone help me with writing a formula that will count how many
enteries
fall between two numbers? Example: How would I find all cells that
have a
value between 4 & 6 in the below scenario? The answer that I'm looking
to
return is 1.

column A
1
5
8
9
Click to expand...
 
M

Myrna Larson

You can use a more complicated COUNTIF formula, but for this problem I would
use two COUNTIF's

=COUNTIF(A1:A4,">=3")-COUNTIF(A1:A4,">6")

If the end points are in cells such as B1 and C1

=COUNTIF(A1:A4,">="&B1)-COUNTIF(A1:A4,">"&C1)
 
K

Kimberly

Thank you for your help!

Myrna Larson said:
You can use a more complicated COUNTIF formula, but for this problem I would
use two COUNTIF's

=COUNTIF(A1:A4,">=3")-COUNTIF(A1:A4,">6")

If the end points are in cells such as B1 and C1

=COUNTIF(A1:A4,">="&B1)-COUNTIF(A1:A4,">"&C1)
Click to expand...
 
K

Kimberly

Thank you for your help!

BenjieLop said:
One way is

=countif(A1:A4,">4")-countif(A1:A4,">6")

NOTE: The ">6" argument is not a typo.

A second way is

=sumproduct(--(A1:A4>4),--(A1:A4<6))
Click to expand...
 

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

Finding the right style field in VBA 0
What Multiple functions do I need? 2
Arriving at totals 2
Finding the proper style field and the numbered paragraph value within the style 0
How to ignore the in between cells? 0
Show value from one cell in another cell if today falls between a date range 5
Help with an if then scenario 2
Excel Countif Function - Calculating Between Two Sets of Values 3

Top