Count with two Conditions

[Buska]

Good Day to All

I got a colum with values like 2 , 3 , 4 , 5 , 9 , 7 , 8 , etc.

I would like to count how many values i got higer then 5 and smalle
then 10.

Can anyone help me plse.

Thanks in advance.

Joao:

 

[Frank Kabel]

Hi
one way:
=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">=10")

or try
=SUMPRODUCT(--(A1:A100>5),--(A1:A100<10))

 

[arron laing]

Frank

Fairly new to the sumproduct command and I was wondering what the -
does in the formula.

I saw one of your previous postings that used the -- to remove tex
from the beginning of numbers (+5, -6 etc), but this does not seem t
be what is happening here.

It also seems to be the same as sumproduct((range>?)*(range>=?)), i
there some nuance I am missing here?

Cheers

Arro

 

[Frank Kabel]

Hi
both syntax versions are valid. Just a matter of taste. As the
multiplication of two conditions coerces the boolean values
(TRUE/FALSE) to numbers (1/0) the same happens with using the double
minus (unaray operator). See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html