Countifs not returning any values...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I don't think a value can be less than -0.05 and more than 0.05.:)
 
Upvote 0
=SUMPRODUCT(COUNTIFS(B21:B29,"MIN",U21:U29,{"<-0.05",">0.05"}))

COUNTIFS is not OR but AND :)
 
Upvote 0
If you mean between -0.05 and 0.05 then try this.

=COUNTIFS(B21:B29,"MIN",U21:U29,">" &-0.05,U21:U29,"<" &0.05)
 
Upvote 0
=COUNTIFS(B21:B29,"MIN",U21:U29,"<-0.05",U21:U29,">0.05")
Norie's suggestion does the same thing, but all you have done wrong is put the < and > signs the wrong way around. Just swap them in your original formula and it should work.
 
Upvote 0
If you mean between -0.05 and 0.05 then try this.

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

Norie;

Your formula works but not entirelly.

It's returning 3 hits rather than 4 hits.


Formula provided by billszysz works fine and returns 4 hits which is the correct count for anything under -5% and over +5%.
=SUMPRODUCT(COUNTIFS(B21:B29,"MIN",U21:U29,{"<-0.05",">0.05"}))


Many thanks to you all for the input and help given.

Much appreciated.
Albert
 
Upvote 0
Norie's suggestion does the same thing, but all you have done wrong is put the < and > signs the wrong way around. Just swap them in your original formula and it should work.

Nories and my own initial formula, after adapting it as per your sugestion, are all returning a value of 3 hits. It should however return 4 hits.

This is the sample data;
U21=2.03%</SPAN>
U22=-
U23=128.77%
U24=-54.57%
U25=1.08%</SPAN>
U26=0.84%</SPAN>
U27=159.39%
U28=10.39%
U29="EMPTY AS A RESULT OF FORMULA =IF(I29=0,0,0.99-L29)

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

What am I missing here?

Many thanks to you all.
 
Upvote 0
Nories and my own initial formula, after adapting it as per your sugestion, are all returning a value of 3 hits. It should however return 4 hits.

This is the sample data;
.
.
.

What am I missing here?
You haven't shown all the sample data (column B is missing).
The most obvious thing would be that one of the values you think should be counted does not have "MIN" in column B
 
Upvote 0
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%.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top