Count number of cells in a range that are >=x or <=-x

[CapRavOr]

I can't seem to figure out why I can't count the number of cells in a range that meet a given criteria. E.g. if the table below was a range A1: D4, count the number of cells >=.25 or <=-.25, should result in 4
My formula: =COUNTIFS(AM78:BC85,">=.25",AM78:BC85,"<=-.25")

.1	.05	.2	.25
.4	.5	.05	.04
.05	.01	.07	.17
.15	.09	.05	.27

 

[dreid1011]

Give this a try:

=COUNTIFS(AM78:BC85,">="&.25,AM78:BC85,"<="&-.25)

 

[Fluff]

How about

=sum(COUNTIFS(AM78:BC85,{">=.25","<=-.25"}))

 

[Joe4]

Give this a try:

=COUNTIFS(AM78:BC85,">="&.25,AM78:BC85,"<="&-.25)

That will always return 0!

The multiple conditions in a COUNTIFS are always treated as an AND, not an OR.
It is impossible for any number to be greater than 0.25 AND less than -0.25 at the same time.

 

[Cubist]

=SUM((AM78:BC85<=-0.25)+(AM78:BC85>=0.25))

 

[dreid1011]

That will always return 0!

The multiple conditions in a COUNTIFS are always treated as an AND, not an OR.
It is impossible for any number to be greater than 0.25 AND less than -0.25 at the same time.

Ah, makes sense. Thanks. I was just focused on the syntax.

 

[Joe4]

Ah, makes sense. Thanks. I was just focused on the syntax.

I must admit, I have been guilty of that myself on a few occasions.
Focused so much on the syntax, I forget to verify the logic!
It happens to all of us!

 

[Scott Huish]

=SUMPRODUCT(--(ABS(AM78:BC85)>=0.25))

 

[awoohaw]

=SUM(--ABS(AM78:BC85)>=.25)

(May need to use CNTL-SHFT-ENTR keystroke if you're using one of your earlier versions of excel)