Counting

[Guest]

I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know
how many are between 1 and 2. Is this a countif? I can't seem to figure out
how to use two conditions.

 

[Biff]

Try this:

=COUNTIF(A1:A10,">=1")-COUNTIF(A1:A10,">2")

Or, use cells to hold the range:

B1 = 1
C1 = 2

=COUNTIF(A1:A10,">="&B1)-COUNTIF(A1:A10,">"&C1)

Biff

 

[Biff]

Try this:

=COUNTIF(A1:A10,">=1")-COUNTIF(A1:A10,">2")

Or, use cells to hold the range:

B1 = 1
C1 = 2

=COUNTIF(A1:A10,">="&B1)-COUNTIF(A1:A10,">"&C1)

Biff

 

[Ragdyer]

When you say *between* 1 and 2, do you mean *greater* then 1 and *less* then
2:

=SUMPRODUCT((A1:A50>1)*(A1:A50<2))

OR, do you mean *greater then or equal* to 1 and *less then or equal* to 2:

=SUMPRODUCT((A1:A50>=1)*(A1:A50<=2))

 

[Ragdyer]

When you say *between* 1 and 2, do you mean *greater* then 1 and *less* then
2:

=SUMPRODUCT((A1:A50>1)*(A1:A50<2))

OR, do you mean *greater then or equal* to 1 and *less then or equal* to 2:

=SUMPRODUCT((A1:A50>=1)*(A1:A50<=2))

 

[Guest]

Thanks to you both.

I see how Biff's works with subtraction.

Although yours looks more direct Ragdyer I'm not clear why it works. I'm
assuming the "*" is not intended to multiply anything and is instead
redifined when used with the Sumproduct function. Is this correct?

 

[Guest]

Thanks to you both.

I see how Biff's works with subtraction.

Although yours looks more direct Ragdyer I'm not clear why it works. I'm
assuming the "*" is not intended to multiply anything and is instead
redifined when used with the Sumproduct function. Is this correct?

 

[Roger Govier]

Hi Adam

Sumproduct is being used to calculate the outcome of 2 arrays.
There are a series of tests which will return True or False, which upon
multiplication will be coerced to 1 or 0
=SUMPRODUCT((A1:A50>=1)*(A1:A50<=2))
(A1:A50>=1) will return either True or False
(A1:A50<=2) will return either True or False

Multiplying them together will only give a result of 1, where both cases
are True (1 * 1), and therefore both conditions are met.
Sumproduct then adds all these results to give your answer.

For more information on Sumproduct (and alternative ways to coerce True
and False to 1 and 0), take a look at Bob Phillips excellent treatise at
http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Roger Govier]

Hi Adam

Sumproduct is being used to calculate the outcome of 2 arrays.
There are a series of tests which will return True or False, which upon
multiplication will be coerced to 1 or 0
=SUMPRODUCT((A1:A50>=1)*(A1:A50<=2))
(A1:A50>=1) will return either True or False
(A1:A50<=2) will return either True or False

Multiplying them together will only give a result of 1, where both cases
are True (1 * 1), and therefore both conditions are met.
Sumproduct then adds all these results to give your answer.

For more information on Sumproduct (and alternative ways to coerce True
and False to 1 and 0), take a look at Bob Phillips excellent treatise at
http://xldynamic.com/source/xld.SUMPRODUCT.html