CODEWITHSUNDEEP
statisticslibreoffice-calc
pleabargain
pleabargain

Reputation: 313

how to count instances between a range in libre office calc 4

I have a range of numbers in a1:80. I want to count the total number of values in a1:a80 that fall between X and Y e.g.

Value X = 30000 value Y = 35000

cell a1 = 34000 cell a42 = 33000

Goal: The formula will tell me that there are 2(two) instances in a1:a80 that have a value between X and Y.

I've tried a variety of combos as below:

=COUNTIF(OR($A1:$A80,"<="&V4)$a1$a80,">="&V3)

and

=IF(OR(">="&V3,"<="&W3),"there are COUNT(1)","there are COUNT(0)")

Here's the video: http://www.youtube.com/watch?v=SKKmlSENNLM

and here's the spreadsheet: https://plus.google.com/116948076340592829238/posts/6dDtKuEebNm

Thank you for considering this problem worthy of your time.

Upvotes: 8

Views: 12189

Answers (3)

user5770374
user5770374

Reputation:

I just solved the same problem in a very simple way, although it took me some time to think of it. Just count all cells that have a value below (either including or excluding) the upper border of the range and subtract the number of cells that have a value below (incl. or excl.) the lower border of the range. For example, if you want to count the cells that contain a value that would be rounded to 9.5.

=COUNTIF(F2:F82,"<9.75")-COUNTIF(F2:F82,"<9.25")

Upvotes: 2

DaN
DaN

Reputation: 91

You may use COUNTIFS, the multi-range, multi-criterion alternative to COUNTIF:

=COUNTIFS(A1:A80;">="&$V$3;A1:A80;"<="&$V$4)

Upvotes: 9

pleabargain
pleabargain

Reputation: 313

I got an answer from a friend

=SUMPRODUCT($A$1:$A$80>=12000,$A$1:$A$80<15199)

I modified is to use a value in the spreadsheet:

=SUMPRODUCT($A$1:$A$80>=U4,$A$1:$A$80<V4)

Upvotes: 1

Related Questions