Re: Excel Countif Function - Calculating Between Two Sets of Values

[Claus Busch]

Hi,

Am Tue, 19 Mar 2013 17:22:21 +0000 schrieb EmzOLV:

> Now for the cells containing a value of less than 5, I've used the
> following countif formula which works: =COUNTIF('RED WET
> 1'!P4:P185,"<=5.00")
>
> For the cells containing a value of 100 or more, I've used the following
> countif formula which works: =COUNTIF('RED WET 1'!P4:P185,">100.01")
>
> Now, the inbetween values are where I am struggling.
>
> For the cells containing a value which lies between 5.01 and 10, I have
> this formula: =COUNTIF('RED WET 1'!P4:P185,">5.01")-COUNTIF('RED WET
> 1'!P4:P185,"<10.00")

try:
=COUNTIF(P4:P185,"<=10")-COUNTIF(P4:P185,"<=5")
or:
=SUMPRODUCT(--(P4:P185>5),--(P4:P185<=10))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Ron Rosenfeld]

On Tue, 19 Mar 2013 17:22:21 +0000, EmzOLV <EmzOLV....@excelbanter.com> wrote:

>
>Hey guys! Hope you can help me - I've been struggling trying to work out
>what is happening with some of my calculations.
>
>Here is my scenario:
>I am trying to identify, between a range of cells (in this case, P5:P185
>on the separate worksheet RED WET 1), how many of these contain a value
>less than 5, a value between 5 and 10, a value between 10 and 20, and
>upwards up until 100, whereby I then want to work out how many cells
>contain a value of 100 or more.
>

For a problem like this, I would use the FREQUENCY function.

=FREQUENCY(P5:P185,{5,10,20,100})

Enter it as an array from, for example A1:A5
As written,
A1: will show the count of values less than or equal to five.
A2: will show the count of values greater than five but less than or equal to ten
A3: will show the count of values greater than ten but less than or equal to 20.
A4: will show the count of values greater than 20 but less than or equal to 100
A5: will show the count of values greater than 100

To enter the formula, first select A1:A5. Enter the formula into the formula bar, then, instead of hitting <ENTER>, hold down <CTRL+SHIFT> while hitting <ENTER>. Excel will copy the same formula into A1:A5 and place curly brackets {...} around the formula.