Forum Discussion
SUM plus countifs
Your formula counts all numbers <=13, being 4
plus
the count of all numbers >= 17, being 5.
Thus, 9
Putting the criteria in curly brackets forces an OR rather than the AND type of function you need. Count all number >= 13 AND <= 17.
Try it this way:
=COUNTIFS(A1:A12,">=13",A1:A12,"<=17") resulting in 7.
I want to see results of =COUNTIFS(A1:A12,">=13",A1:A12,"<=17") for individual row.
Eg if i can add countif or a filter to indicate yes/true infront of every number which is '>=13' or '<=17' & no infront of the rest.
ajaydeshmukh Not sure I follow, but perhaps the attached file contains what you want.
ajaydeshmukh But that's completely different from what you asked earlier. Perhaps the formula below will do what you need. Don't bother the to use IF, as this formula will return TRUE or FALSE.
=OR(AND(A1>=1,A1<=6),AND(A1>=13,A1<=17))
- I want to have multiple ranges. as i am given a task to find out true or false value.
Eg every value which is (>=1,<=6,>=13,<=17) should be yes/true and rest all false.
In same way I have 10 different ranges (1-6,13-17,etc)
I hope I was able to convey my question ajaydeshmukh That formula makes no sense as the OR part doesn't add any relevance. The formula returns "true" if all of the three conditions are met:
A should be >=1 AND <=6 (that returns all from 1 to 6)
AND
A should be either >=1 OR<= 6 (that returns all numbers as all of them are >=1 or <=6.
Joining the two will still just get you numbers between 1 and 6.
So, what is it that you want to achieve?
Riny_van_Eekelen Can I use Multiple ranges in =IF(AND(A1>=13,A1<=17),"true","false") ?
I tried with =IF(AND(A1>=1,A1<=6,OR(A1>=1,A1<=6)),"true","false") but, it is following only one range out of two.
eg.
- Thankyou, it really helped a lot.