Can anybody help me with the following formula.
=COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).

I get an error everytime I try to put it in.


tx.

You can use two COUNTIFs. E.g.,

=COUNTIF(C2:C1300,"<=500")-COUNTIF(C2:C1300,"<0")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (E-Mail Removed)



"nkob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can anybody help me with the following formula.
> =COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).
>
> I get an error everytime I try to put it in.
>
>
> tx.
>
>
>

nkob,

Use 2 countif's for this

=COUNTIF(C2:C1300,">=0") - COUNTIF(C2:C1300,">500")
gets the range 0<=C2:C1300<=500
Count the number greater than or equal to 0 and subtract
the number greater than 500.

Or use 1 sumproduct
=SUMPRODUCT((C2:C1300<>"")*(C2:C1300>=0)*(C2:C1300<=500))

Dan E

"nkob" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Can anybody help me with the following formula.
> =COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).
>
> I get an error everytime I try to put it in.
>
>
> tx.
>
>
>

=COUNTBETWEEN(C2:C1300,0,500) where COUNTBETWEEN is below:

Function COUNTBETWEEN(rng, valLow, valHigh, _
Optional inclLow As Boolean = True, _
Optional inclHigh As Boolean = True)
Select Case inclLow & inclHigh
Case "TrueTrue"
COUNTBETWEEN = Application.CountIf(rng, ">=" & valLow) _
- Application.CountIf(rng, ">" & valHigh)
Case "FalseFalse"
COUNTBETWEEN = Application.CountIf(rng, ">" & valLow) _
- Application.CountIf(rng, ">=" & valHigh)
Case "FalseTrue"
COUNTBETWEEN = Application.CountIf(rng, ">" & valLow) _
- Application.CountIf(rng, ">" & valHigh)
Case "TrueFalse"
COUNTBETWEEN = Application.CountIf(rng, ">=" & valLow) _
- Application.CountIf(rng, ">=" & valHigh)
End Select
End Function

Alan Beban

nkob wrote:
> Can anybody help me with the following formula.
> =COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).
>
> I get an error everytime I try to put it in.
>
>
> tx.
>
>
>

can i do the same with a sum. i want to sum up the total if the two
criteria match.


"Dan E" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> nkob,
>
> Use 2 countif's for this
>
> =COUNTIF(C2:C1300,">=0") - COUNTIF(C2:C1300,">500")
> gets the range 0<=C2:C1300<=500
> Count the number greater than or equal to 0 and subtract
> the number greater than 500.
>
> Or use 1 sumproduct
> =SUMPRODUCT((C2:C1300<>"")*(C2:C1300>=0)*(C2:C1300<=500))
>
> Dan E
>
> "nkob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > Can anybody help me with the following formula.
> > =COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).
> >
> > I get an error everytime I try to put it in.
> >
> >
> > tx.
> >
> >
> >
>
>

For SUMIF's
=SUMIF(C2:C1300,">=0") - SUMIF(C2:C1300,">500")

For SUMPRODUCT
=SUMPRODUCT((C2:C1300)*(C2:C1300>=0)*(C2:C1300<=500))

Dan E

"nkob" <(E-Mail Removed)> wrote in message news:#(E-Mail Removed)...
> can i do the same with a sum. i want to sum up the total if the two
> criteria match.
>
>
> "Dan E" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > nkob,
> >
> > Use 2 countif's for this
> >
> > =COUNTIF(C2:C1300,">=0") - COUNTIF(C2:C1300,">500")
> > gets the range 0<=C2:C1300<=500
> > Count the number greater than or equal to 0 and subtract
> > the number greater than 500.
> >
> > Or use 1 sumproduct
> > =SUMPRODUCT((C2:C1300<>"")*(C2:C1300>=0)*(C2:C1300<=500))
> >
> > Dan E
> >
> > "nkob" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > > Can anybody help me with the following formula.
> > > =COUNTIF(AND(C2:C1300,"<=500"),(C2:C1300),">=0")).
> > >
> > > I get an error everytime I try to put it in.
> > >
> > >
> > > tx.
> > >
> > >
> > >
> >
> >
>
>