countif functions

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.

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

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

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
"--"



In article <D5894C0B-5B47-4361-83F2-C107BB1D1FE1@microsoft.com>,
"bsantona" <bsantona@discussions.microsoft.com> wrote:

> 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.

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

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

"bsantona" wrote:

> 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.

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!


"JE McGimpsey" wrote:

> 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
> "--"
>
>
>
> In article <D5894C0B-5B47-4361-83F2-C107BB1D1FE1@microsoft.com>,
> "bsantona" <bsantona@discussions.microsoft.com> wrote:
>
> > 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.
>

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

"bpeltzer" wrote:

> 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
>
> "bsantona" wrote:
>
> > 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.

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




"bsantona" <bsantona@discussions.microsoft.com> wrote in message
news:8F016C14-B6CE-4CF5-8553-5050BEDD8699@microsoft.com...
> 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!
>
>
> "JE McGimpsey" wrote:
>
> > 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
> > "--"
> >
> >
> >
> > In article <D5894C0B-5B47-4361-83F2-C107BB1D1FE1@microsoft.com>,
> > "bsantona" <bsantona@discussions.microsoft.com> wrote:
> >
> > > 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.
> >

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.

"bsantona" wrote:

> 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
>
> "bpeltzer" wrote:
>
> > 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
> >
> > "bsantona" wrote:
> >
> > > 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.

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.




In article <8F016C14-B6CE-4CF5-8553-5050BEDD8699@microsoft.com>,
"bsantona" <bsantona@discussions.microsoft.com> wrote:

> 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.

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.
"bsantona" <bsantona@discussions.microsoft.com> wrote in message
news:D5894C0B-5B47-4361-83F2-C107BB1D1FE1@microsoft.com...
> 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.