Greetings,
I am trying to select only rows with values 1 through 5
I have 50+ rows of data over columns A:R
Each cell has a value range of 0,1,2,3,4,5, or 99
I have used:
=IF(OR(A2:R2=0,A2:R2=99),0,1) Doesn't work
=COUNT(IF(OR(A2:R2=0,A2:R2=99),0,1)) Doesn't work
=COUNTIFS(A2:R2,0,A2:R2,99) Doesn't work
=IF(OR(a2=0,a2=99,...,r2=0,r2=99),0,1) Worked
Thanks in advance!
Is it something like this?
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
jam320,
Can you attach a workbook with example data and with the desired result. Thanks.
BrownBoy
If happy, mark "SOVLED" & add to "REP"
Jacc forgot the possibility of 0.
Something like this
=IF(COUNTIF(A1:R1,">5")+COUNTIF(A1:R1,"<1"),"","SELECT")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Chemist B, you are embarrassing me! To compensate for that I had to come up with a formula of unheard of spectacularity.
Behold the amazingness of:Formula:
Please Login or Register to view this content.
No Ctrl + Shift + Enter needed!
neat, is that a reverse division in the 0\99? Never seen one of those before!
:P
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Eeeeeeh... it was really windy here when I posted it so the | fell over like so \.
hehe.
I don't quite understand the {0,99} in the formula, however
=IF(SUM(INDEX(COUNTIF(A1:F1,{-1,99}),0)),"-","Selected")
seems to pick up the zeros
It's really an array formula in disguise. Remove the INDEX and you are back to doing the three finger, salute Ctrl + Shift + Enter.
So it's counting the instances of 0 and 99 and sums them up. As long as the sum is 0 there are no 0 or 99 present and the row is selected.
Your Excel needs -1 to pick up the 0's? I'm not sure, is it me or is it you this time?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks