AND

[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

 

[Chip Pearson]

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)

 

[Don Guillett]

another- NO need to array enter. Enter as a regular formula.
=sumproduct((a1:a10>10)*(a1:a10<30))

 

[Myrna Larson]

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.

 

[Harlan Grove]

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})

...

At which point there's no benefit compared to

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

which doesn't need to be array-entered.

 

[Harlan Grove]

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})

...

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.

 

[Thomas]

...
..

At which point there's no benefit

Benefits one if you're not using a version with SUMPRODUCT.

compared to

=SUMPRODUCT((A1:A10>2)*(A1:A10<10))
which doesn't need to be array-entered.

If thats the case just use
SUM((A1:A10>10)*(A1:A10<30))
array entered
which is not version dependent

 

[Thomas]

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.

 

[Harlan Grove]

...
...

Benefits one if you're not using a version with SUMPRODUCT.

...

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?

 

[Harlan Grove]

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.

...

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

 

[Thomas]

Guess because I never used sumproduct before XL97,Just noticed it is in
95 though.