I am trying to count how many times a number >=94 and <95 occurs in a column?
I can get it to count how many above a number or below but not in between.
I am trying to count how many times a number >=94 and <95 occurs in a column?
I can get it to count how many above a number or below but not in between.
Assuming that you name your range of cells "range", use this array formula:
=SUM((range>=94)*(range<95)). Because this is an array formula, you can not just hit the enter key to enter the formula in the cell, rather you have to use the key combination of the Control Key, Shift Key and Enter Key, holding them down in that order till all three buttons are depressed at the same time. I usually name my ranges rather than typing in the range, for example, if starting in cells A1:A5, if you enter in:
94
93
96
94.5
94.7
in cells A1 to A5 respectively, the formula will give you the result of 3 (94, 94.5 & 94.7 fit the criteria).
Without using a named range, the formula would be:
=SUM((A1:A5>=94)*(A1:A5<95)), then use Ctrl+Shift+Enter to enter the formula in the cell. Be sure you get the parenthesis correct. The Asterisk (*)is the symbol between the 2 conditions. Terri
a couple of other ways....
=COUNTIF(A:A,"<95")-COUNTIF(A:A,"<94")
or
=SUMPRODUCT(--(A1:A100<95),--(A1:A100>=94))
Note: SUMPRODUCT can't use whole column references like COUNTIF.
Variations of these could be used for counting between any two numbers but in your specific case, because your range from one integer to another you could also try
=SUMPRODUCT(--(INT(A1:A100)=94))
=SUM(COUNTIF(A:A,{">=94",">95"})*{1,-1})Originally Posted by Kblue74
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks