Query on COUNTIFS Formula

[ketan.parekh27]

I have a query for a specific situation. I am trying to determine the no. of clients having Loss in a range of cells. Have applied "COUNTIFS" formula but is not considering the figures in NEGATIVE "-". For E.G. I want to know how many clients have negative returns more than 18%, in this case it picks clients having POSITIVE returns above 18% also, it is not considering cells with NEGATIVE returns. I need a formula to counter this problem.


Please help me if there is any formula to counter this query.

 

[NARAYANK991]

Hi Ketan ,


Use the following construct :


=COUNTIFS(Category1,Criterion1,Returns_Range,">=18")+COUNTIFS(Category1,Criterion1,Returns_Range,"<=-18")


Since the two COUNTIFS are mutually exclusive , there is no danger of any figure being counted twice.


Narayan

 

[ketan.parekh27]

Hi Narayan,


Thx a million. I will test with the provided suggestion & formula and revert in case I am lost again, once again, Thx a lot.


Ketan

 

[Excel_Kid_663]

Erm, maybe im not reading this right but that is not the syntax of a countifs formula NARAYANK991. A count ifs formula looks like this:


=COUNTIFS(Criteria_Range1, Criteria1, Criteria_Range2, Critera2) etc.


However getting back to the problem at hand, your formula is very simple if im reading your post right, you only need COUNTIF not COUNTIFS because you are only searching one criteria:


If you data is column A and you were searching the whole column it would look like this:


=CONTIF(A:A,"<-18")

You could even go one step further and make it dynamic on the fly. Instead of saying "<-18" link it to a cell reference and you can then change what you want to see by putting a value in the cell instead of changing the formula. So the formula would be:


=COUNTIF(A:A,"<"&B1) Then you can change the value in B1 to -18 or 18 and even link it to a validation list if you wish.


You could even take it a step further and use COUNTIFS to select between a range like this:


=COUNTIFS(A:A,"<10",A:A,">0")


or do it dynmically


=COUNTIFS(A:A,"<"&B2,A:A,">"&C2)


I'm pretty sure thats what you need.


Is it sad i enjoy this? lol