AND

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I would like to do a COUNTIF with two parameters. ie >10
and <30. I can do one or the other but I can not figure
out how to do them both in one formula. Any suggestions.

Thanks in advance.

Joe
 
Joe,

Try something like

=COUNTIF(A1:A10,"<30")-COUNTIF(A1:A10,"<=10")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
another- NO need to array enter. Enter as a regular formula.
=sumproduct((a1:a10>10)*(a1:a10<30))
 
That formula doesn't work for me. I put some numbers between 1 and 50 in A1:A10. There were just
4 between 11 and 29, inclusive. There are 7 > 10 and 7 < 30.

Your formula (pasted from the ng message) and array entered, gives 14.

That's because you are doing the equivalent of OR rather than AND. The first segment of your
formula counts numbers >10, but NOT necessarily <30. The 2nd, numbers <30, but NOT necessarily >
10.
 
I guess it would depend how you wanted the parameters counted,An
'between' or 'all'."If you wanted an AND instead of an OR then this one
should work,
SUM(COUNTIF(A1:A10,{">2","<10","<>"})*{1,1,-1})
Click to expand...
...

At which point there's no benefit compared to

=SUMPRODUCT((A1:A10>2)*(A1:A10<10))

which doesn't need to be array-entered.
 
I guess it would depend how you wanted the parameters counted,An
'between' or 'all'."If you wanted an AND instead of an OR then this one
should work,
SUM(COUNTIF(A1:A10,{">2","<10","<>"})*{1,1,-1})
Click to expand...
...

Also, the "<>" test will pick up any nonblank entries in A1:A10. If there were
any text or error values in A1:A10, this formula will understate the true count
of matching numbers.
 
Harlan said:
...
..

At which point there's no benefit
Click to expand...

Benefits one if you're not using a version with SUMPRODUCT.
compared to
Click to expand...
=SUMPRODUCT((A1:A10>2)*(A1:A10<10))
which doesn't need to be array-entered.
Click to expand...


If thats the case just use
SUM((A1:A10>10)*(A1:A10<30))
array entered
which is not version dependent
 
Safe to say you might want to put in a ISNUMBER check in whatever
formula you use if you're expecting garbage in the range.
 
...
...
Benefits one if you're not using a version with SUMPRODUCT.
Click to expand...
...

Which would be?

IIRC, SUMPRODUCT was in Excel 3.0 (as a response to 123 including @SUMPRODUCT in
123 Release 3.0). COUNTIF came later.

And your point is?
 
Safe to say you might want to put in a ISNUMBER check in whatever
formula you use if you're expecting garbage in the range.
Click to expand...
...

Untrue.

=SUM(COUNTIF(A1:A10,{">2","<10"}))-COUNT(A1:A10)

would be sufficient, but it's very, very difficult to see why anyone would
prefer to use it instead of

=COUNTIF(A1:A10,">2")-COUNTIF(A1:A10,">=10")
 
Back
Top