countif functions

[Guest]

I'm trying to get a count of cells that have a range of data for instance I
want a count of cells that have data between 10 - 20.

Any ideas would be appreciated.

 

[JE McGimpsey]

One way:

=COUNTIF(rng,">=10")-COUNTIF(rng, ">20")

Another:

=COUNTIF(rng,"<=20") - COUNTIF(rng, "<10")

Another:

=SUMPRODUCT(--(rng>=10),--(rng<=20))

see http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of
"--"

 

[John Michl]

Try
=SUMPRODUCT((A1:A10>=10)*(A1:A6<=20))

For info on sumproduct see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

- John
www.JohnMichl.com

 

[John Michl]

Try
=SUMPRODUCT((A1:A10>=10)*(A1:A10<=20))

For info on sumproduct see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

- John
www.JohnMichl.com

 

[John Michl]

Disregard first post. The second range should be the same as the first.

 

[Guest]

That would be the count of cells >=10 minus the count of cells >20. So
=countif(a:a,">=10")-countif(a:a,">20"). (Note that this includes cells
equal to 10 and those equal to 20; if you only want one endpoint included,
adjust the comparison operators accordingly).
HTH. --Bruce

 

[Guest]

still not working correctly. I have 12 cells that have data, of the 12 cells
2 have the data range between 10 - 20 so I'm looking for the formula to give
me a count of 2 but the formulas below give me a count of 10 which makes
sense since it's subtracting.

Please help!

 

[Guest]

but I don't want to minus the cells, I just want a count of how many cells
are between the data range, for example I have 12 cells of which 2 have data
between 10 - 20 so the formula should return 2.

Please help

 

[Peo Sjoblom]

But it is subtracting greater than or equal to 20 from greater than or equal
to 10 which is correct, the error must be between the chair and the
computer, to show what I mean
assume these are the 12 values and they are in A1:A12
1
2
3
4
5
12
19
21
22
23
24
25

now

=COUNTIF(A1:A12,">=10")

will return 7 (12, 19, 21, 22, 23, 24, 25)

=COUNTIF(A1:A12,">=20")

will return 5 (21, 22, 23, 24, 25)

thus

=COUNTIF(A1:A12,">=10)-COUNTIF(A1:A12,">=20")

which is the same as

=7-5

will return 2


--

Regards,

Peo Sjoblom

 

[Guest]

I think you do want to subtract the second range. Let's consider your
example a little deeper. You have 12 cells, 2 are in the range 10-20.
Suppose 4 are in the range 20+. Then there would be 6 in the range 10+. So
6 (in the 10+ range) minus 4 (in the 20+ range) leaves 2 in the target 10-20
range. That's pretty much what the suggested formula does: count the 10+'s
then subtract the 20+'s.

 

[JE McGimpsey]

If you really only have 2 cells that are between 10 and 20, inclusive,
then all three of the formulae I gave you will return 2.

 

[Roger H.]

Try : =COUNTIF(Range,">10") - COUNTIF(Range,">20") As I understand your
question, you want a count of the number of cells that have a a value
*between* (!) 10 and 20 ( that are equal to 11 as a minimum and equal to 19,
as a maximum).I think this will do the job for you.