# 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!!!

2. ## Re: if criteria count or empty

I think this is what you want:

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

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

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))

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))

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

7. ## Re: if criteria count or empty

iam not so sure)

8. ## Re: if criteria count or empty

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

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

10. ## Re: if criteria count or empty

ok, iwill try also post#5

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

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

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))

14. ## Re: if criteria count or empty

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

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.

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).

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).

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

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))

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

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.

22. ## Re: if criteria count or empty

yes is working !!!thanks so much

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1