# if criteria count or empty

1. ## if criteria count or empty

I need aformula at F2 and draga down ,which work like:if A2 is a number and in K1:K18 there are numbers(in case I put manually) and A2 matches in the range of K1:K18 write 1 if not exist write 0. the most important is when there no numbers at K:1K18 should show embty/blank.!! so the formula works only in case there are numbers at K1:k18.
thanks so much for your help at this forum!!!  Register To Reply

2. ## Re: if criteria count or empty

I think this is what you want:

=IFERROR(IF(\$A2="","",MATCH(A2,\$K\$1:\$K\$18,0)),"")  Register To Reply

3. ## Re: if criteria count or empty

i make an example:lets say we are at spin 10(A10=8) and the same time at K1:K18 there are numbers ,if 8 is in the range ofK1:K18 formula should show 1if no 0,at the next spin the same..,the formula should takeinto consideration the existing of numbers in K1:K18(becouse in case i dont put numbers there manually ,the formula at F1:F36 should show empty  Register To Reply

4. ## Re: if criteria count or empty

=IF(OR(\$A2="",COUNT(\$K\$1:\$K\$18)=0),"",IF(ISERROR(MATCH(A2,\$K\$1:\$K\$18,0)),0,1))  Register To Reply

5. ## Re: if criteria count or empty

Or maybe this:

=IF(OR(\$A2="",COUNT(\$K\$1:\$K\$18)=0),"",IF(ISERROR(MATCH(A2,\$K\$1:\$K\$18,0)),0,A2))

or this:

=IF(OR(\$A2="",COUNT(\$K\$1:\$K\$18)=0),"",IF(ISERROR(MATCH(A2,\$K\$1:\$K\$18,0)),"",A2))  Register To Reply

6. ## Re: if criteria count or empty

the formula should take into consideration A:A(spins) together with the range k1:k18 and count only if there are numbers in k1:k18, if the spins continuew and there are no munbers at k1:k18 should show empty  Register To Reply

7. ## Re: if criteria count or empty

iam not so sure )  Register To Reply

8. ## Re: if criteria count or empty

OK, then use the second option in post #5, which seems to match your last attachment.  Register To Reply

9. ## Re: if criteria count or empty

i have another example with a forula i you gave me be fore some months,the problem with this formula it shows 0 if there are no numbers T K1:K18 and it should show empty,ineed to change the formula to empty,please see attached file  Register To Reply

10. ## Re: if criteria count or empty

ok, iwill try also post#5  Register To Reply

11. ## Re: if criteria count or empty

i check post 5# the second form ,yea.., it shows empty when the number is not in the range but it should show 0,becouse what i need is ::i want to count the 0s later with another formula(the zeros in case there are numbers atK1:K18 always,in case the number at A:A is not in range
i hope you understand me)),thank you so much  Register To Reply

12. ## Re: if criteria count or empty

maybe we can compine two formula? i mean the second is :if there is a number at K1:K18 start to count ,i am dont know ,is hard for me vey difficult  Register To Reply

13. ## Re: if criteria count or empty

Try this (last attempt):

=IF(OR(\$A2="",COUNT(\$K\$1:\$K\$18)=0),0,IF(ISERROR(MATCH(A2,\$K\$1:\$K\$18,0)),0,A2))  Register To Reply

14. ## Re: if criteria count or empty

i dont know what to do, maybe what iam asking is not possible to excel  Register To Reply

15. ## Re: if criteria count or empty

I doubt it, but you are not making a very good job of clearly explaining what you want.   Register To Reply

16. ## Re: if criteria count or empty

i agree.i will try one more time:if there are numbers at range k1:k18 start counting and if there are no numbers the cells to be embty.only if thre are numbers at k:k the formula should start work
counting means that:the formula look at roulette spins at A:A and if the the last spin(number) exist in the numbers at k1:k18 to show 1 ,otherwise zero(0).

(the final thing or aim is that i want to know how many times the numbers at A:A is not in the range of K1:18.so if we have 4 zeros for example in a row it will mean that 4 times did not win)

important note:the K1:K18 some times is embty or there are 18 numbers which i put manually whenever i want,the formula should be smart)) to know wheh there are numbers there).  Register To Reply

17. ## Re: if criteria count or empty

What do you mean by the last spin number? What are we supposed to be counting?

important note:the K1:K18 some times is embty or there are 18 numbers which i put manually whenever i want,the formula should be smart)) to know wheh there are numbers there).  Register To Reply

18. ## Re: if criteria count or empty

spin are goes to A100 lets say,so when there are numbers at k1:k18 and the lat number at spins range is in not the numbers of k1:k18 count 0,then if the next number is also not in k1:k18 count 0 (so we have to 0s 0 ,if exists write 1 ,if there are no numbers =embty
0  Register To Reply

19. ## Re: if criteria count or empty

It this does NOT do what you want, provide a workbook with this formula in place and annotate it to show where it goes wrong.

=IF(OR(\$A2="",COUNT(\$K\$1:\$K\$18)=0),"",IF(ISERROR(MATCH(A2,\$K\$1:\$K\$18,0)),0,1))  Register To Reply

20. ## Re: if criteria count or empty

yes Ali its ok but it the trick is at th following example :lets say that th 19 th spin number is the result that will make me put manually 18 random numbers at k1:k18,i want the formula to start counting from that event otherwise i will not be able to count how many lose i will have from the time that numbers exist in k1:k18  Register To Reply

21. ## Re: if criteria count or empty

lets say that th 19 th spin number is the result that will make me put manually 18 random numbers at k1:k18,i want the formula to start counting from that event
A formula cannot do this: it can only work based on what is there or not there, not on when that data is added.

Sorry, but you are going to need to use VBA coding for this.

I cannot help you any further. Because your thread title is so poor given what we now know about your requirements, I am going to mark this thread as SOLVED and ask you to start again, with a MUCH better tttle AND a MUCH better description of what you want in the VBA section of the forum.  Register To Reply

22. ## Re: if criteria count or empty

yes is working !!!thanks so much  Register To Reply