COUNTIF between values

[JW]

Hi, I've struggled with this and couldn't
see it in a previous thread, so over to the experts

I have a column of figures and I would like to use COUNTIF to count all the values between 1 and -1. How can I do this, rather than adding another IF column and counting the results.

Thanks in advance

[Ian Mac]

On 2002-05-22 04:32, JW wrote:
Hi, I've struggled with this and couldn't
see it in a previous thread, so over to the experts

I have a column of figures and I would like to use COUNTIF to count all the values between 1 and -1. How can I do this, rather than adding another IF column and counting the results.

Thanks in advance

Try;

=SUMPRODUCT((A1:A100<=1)*(A1:A100>=-1))

[Iain Lewis]

Or :

=COUNT(IF(C1:C10<=1,IF(C1:C10>=-1,C1:C10)))

entered as an array ( control + shift + enter )

[Albert 1]

I'm not sure what you're asking for.
If you mean the number of cells, Ian mAc's answer is correct.
If you mean the sum of the values between -1 en +1 (so -1 and +1 not included), you can use: =SUMIF(A1:A10,">=-1")-SUMIF(A1:A10,">1")

[ This Message was edited by: Albert 1 on 2002-05-22 05:10 ]

[Ian Mac]

On 2002-05-22 05:09, Albert 1 wrote:
I'm not sure what you're asking for.
If you mean the number of cells, Ian mAc's answer is correct.
If you mean the sum of the values between -1 en +1 (so -1 and +1 not included), you can use: =SUMIF(A1:A10,">=-1")-SUMIF(A1:A10,">1")

[ This Message was edited by: Albert 1 on 2002-05-22 05:10 ]

or alternatively:

=SUMPRODUCT((A1:A100<=1)*(A1:A100>=-1),A1:A100)

[JW]

Thanks guys, I've used Ian Mac's SUMPRODUCT which has worked just fine. I'll try out the array version later.