Countifs not returning any values...

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 30, 2014

• #2

I don't think a value can be less than -0.05 and more than 0.05.

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• Oct 30, 2014

• #3

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

COUNTIFS is not OR but AND

 

Upvote 0

albertc30

Well-known Member

Joined

May 7, 2012

Messages

1,091

Office Version

1. 2019

Platform

1. Windows

• Oct 30, 2014

• #4

sORRY.

I meant between.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 30, 2014

• #5

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

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Oct 30, 2014

• #6

albertc30 said:

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

Click to expand...

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

albertc30

Well-known Member

Joined

May 7, 2012

Messages

1,091

Office Version

1. 2019

Platform

1. Windows

• Oct 30, 2014

• #7

Norie said:

If you mean between -0.05 and 0.05 then try this.

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

Click to expand...

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

albertc30

Well-known Member

Joined

May 7, 2012

Messages

1,091

Office Version

1. 2019

Platform

1. Windows

• Oct 30, 2014

• #8

Peter_SSs said:

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.

Click to expand...

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

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Oct 30, 2014

• #9

albertc30 said:

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?

Click to expand...

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

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 30, 2014

• #10

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