countif

[nkob]

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.

 

[Chip Pearson]

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 address removed)

 

[Dan E]

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

 

[Alan Beban]

=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]

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

 

[Dan E]

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

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

Dan E