CountIf Function

[Barb Miles]

I have a range of numbers between 1 and 25 in 50 cells.

How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?

Or if there is another function to use, let me know.

Thank you.
--
Barb

[Bondi]

Barb Miles wrote:
> I have a range of numbers between 1 and 25 in 50 cells.
>
> How could I use the countif function to look at that range of numbers for
> values between 5 and 10 and count those values that meet the criteria of
> between 5 and 10?
>
> Or if there is another function to use, let me know.
>
> Thank you.
> --
> Barb

Hi Barb,

Maybe you can use the Sumproduct()

Something along the lines of:

=SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))

Regards,
Bondi

[bpeltzer]

Use countif twice, once to count those that are >=5 and a second time to
count those that are >10. The difference is the count of those in the range
[5,10]:
=countif(range,">=5")-countif(range,">10")

"Barb Miles" wrote:

> I have a range of numbers between 1 and 25 in 50 cells.
>
> How could I use the countif function to look at that range of numbers for
> values between 5 and 10 and count those values that meet the criteria of
> between 5 and 10?
>
> Or if there is another function to use, let me know.
>
> Thank you.
> --
> Barb

[Barb Miles]

Doesn't the sumproduct function ADD the values, not count them?
--
Barb


"Bondi" wrote:

>
> Barb Miles wrote:
> > I have a range of numbers between 1 and 25 in 50 cells.
> >
> > How could I use the countif function to look at that range of numbers for
> > values between 5 and 10 and count those values that meet the criteria of
> > between 5 and 10?
> >
> > Or if there is another function to use, let me know.
> >
> > Thank you.
> > --
> > Barb
>
> Hi Barb,
>
> Maybe you can use the Sumproduct()
>
> Something along the lines of:
>
> =SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))
>
> Regards,
> Bondi
>
>

[patele]

Enter the following

=SUM((((A1:A20)<10)+((A1:A20))>5)*1)

After entering the above you need to press the Ctrl, Shift and Enter keys to enter it as an array.

[Bondi]

Barb Miles wrote:
> Doesn't the sumproduct function ADD the values, not count them?
> --
> Barb
>
>
> "Bondi" wrote:
>
> >
> > Barb Miles wrote:
> > > I have a range of numbers between 1 and 25 in 50 cells.
> > >
> > > How could I use the countif function to look at that range of numbers for
> > > values between 5 and 10 and count those values that meet the criteria of
> > > between 5 and 10?
> > >
> > > Or if there is another function to use, let me know.
> > >
> > > Thank you.
> > > --
> > > Barb
> >
> > Hi Barb,
> >
> > Maybe you can use the Sumproduct()
> >
> > Something along the lines of:
> >
> > =SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))
> >
> > Regards,
> > Bondi
> >
> >

Hi,
Well it does sum the products. But in this case the products will be 1
when both conditions are met and 0 if none or only one of the
conditions are met. So it will sum all the 1's where both conditions
are met and hence function somthing like a counting function.

Regards,
Bondi

[Barb Miles]

This does not work. It returns an answer of one more than the answer should
be.
--
Barb


"bpeltzer" wrote:

> Use countif twice, once to count those that are >=5 and a second time to
> count those that are >10. The difference is the count of those in the range
> [5,10]:
> =countif(range,">=5")-countif(range,">10")
>
> "Barb Miles" wrote:
>
> > I have a range of numbers between 1 and 25 in 50 cells.
> >
> > How could I use the countif function to look at that range of numbers for
> > values between 5 and 10 and count those values that meet the criteria of
> > between 5 and 10?
> >
> > Or if there is another function to use, let me know.
> >
> > Thank you.
> > --
> > Barb

[patele]

Barb,

Don't know if you found the answer yet but here a formula that should work for you. Let me know if it does.

=COUNTIF(A1:A50,">=5")-COUNTIF(A1:A50,">10")

Ed

[excesspotential]

I have a similar issue.

I am trying to count two columns with different data.


=countif(E5:E10,"=F") and (F5:F10,"=X")
=countif(E5:E10,"=M") and (F5:F10,"=X")

I am not getting any results

Jim

[Maistrye]

I have a similar issue.

I am trying to count two columns with different data.


=countif(E5:E10,"=F") and (F5:F10,"=X")
=countif(E5:E10,"=M") and (F5:F10,"=X")

I am not getting any results

Jim

Basically, just copy what Bondi stated up above:

=SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))

except twist it into:

=SUMPRODUCT(--(E5:E10="F"),--(F5:F10="X"))
=SUMPRODUCT(--(E5:E10="M"),--(F5:F10="X"))

Scott

[excesspotential]

Outstanding

[tz81c1]

Yes I agree, OUTSTANDING! I've read many threads looking for a simple formula like this that works. Well Done!