Countifs not returning any values...

[albertc30]

Hi all.

Formula below is, to my eyes correct in the sense it will evaluate data and count anything it finds, greater than -5.0% and +5.0%.

=COUNTIFS(B21:B29,"MIN",U21:U29,"<-0.05",U21:U29,">0.05")

Even though I have 4 values that meet the criteria, no results, or errors, are returned.

Should I quit now?

Any help is trully appreciated.

Regards,
Albert

 

[Peter_SSs]

Albert

In post #4 you said 'between', I would assume that would mean between -5% and 5%, not less than -5% or more than 5%.

Hmm, yes, I had missed post #7. It certainly seems to contradict post #4.

 

[albertc30]

Sorry Peter.

Column B is all MIN apart from line 29 which is empty.

 

[albertc30]

Again, my bad, sorry.

I got confused with my own doings as I have formulas for both.

For the between & equal to -5% & +5% I got;
=COUNTIFS(B21:B29,"MIN",U21:U29,">=-0.05",U21:U29,"<=0.05")
The above formula works fine.

For the higher than -5% & +5% I got;
=COUNTIFS(B21:B29,"MIN",U21:U29,">0.05")+COUNTIFS(B21:B29,"MIN",U21:U29,"<-0.05")
The above works fine. I was now wondering if there's any way to make this formula shorter.

I also tried to do it trying to use 3 ranges but then it failled to return a value. In the end I just used countifs + countifs.

Apologies again for you all kind good people, and many thanks for helping, much appreciated.

Any more sugestions please send them my way.

Regards,
Albert

 

[Peter_SSs]

For the between & equal to -5% & +5% I got;
=COUNTIFS(B21:B29,"MIN",U21:U29,">=-0.05",U21:U29,"<=0.05")
The above formula works fine.

Any more sugestions please send them my way.

I don't know where your formulas are located but say that first on is in cell A21, then try this for the other result:

=COUNTIFS(B21:B29,"MIN",U21:U29,"<>")-A21

I've assumed that cells in U21:U29 contain numbers or are blank.

 

[Jonmo1]

For the higher than -5% & +5% I got;
=COUNTIFS(B21:B29,"MIN",U21:U29,">0.05")+COUNTIFS(B21:B29,"MIN",U21:U29,"<-0.05")
The above works fine. I was now wondering if there's any way to make this formula shorter.

Try
=SUM(COUNTIFS(B21:B29,"MIN",U21:U29,{">0.05","<-0.05"}))

 

[albertc30]

I don't know where your formulas are located but say that first on is in cell A21, then try this for the other result:

=COUNTIFS(B21:B29,"MIN",U21:U29,"<>")-A21

I've assumed that cells in U21:U29 contain numbers or are blank.

I see what you are saying. I have done it at some point and it works.

 

[albertc30]

Try
=SUM(COUNTIFS(B21:B29,"MIN",U21:U29,{">0.05","<-0.05"}))

Works great Jonmo1.

Thanks to all for the support and help given.

Regards,
Albert

 

[Jonmo1]

You're welcome

 

[Aladin Akyurek]

Works great Jonmo1.

Thanks to all for the support and help given.

Regards,
Albert

Post #3 gives you essentially the same thing. It allows you to have interval boundaries in cells of their own.