+ Reply to Thread
Results 1 to 22 of 22

if criteria count or empty

  1. #1
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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!!!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: if criteria count or empty

    I think this is what you want:

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: if criteria count or empty

    Try this instead:

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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    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. #6
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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. #7
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    Re: if criteria count or empty

    iam not so sure)

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: if criteria count or empty

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

  9. #9
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    Re: if criteria count or empty

    ok, iwill try also post#5

  11. #11
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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. #12
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    Re: if criteria count or empty

    You keep changing your requirements!

    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. #14
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    Re: if criteria count or empty

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

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    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. #16
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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).
    Last edited by zzz444; 08-02-2020 at 11:01 AM.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    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).
    Already covered in three of the formulae I've already given you.
    Last edited by AliGW; 08-02-2020 at 11:10 AM.

  18. #18
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    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))
    Attached Files Attached Files
    Last edited by AliGW; 08-02-2020 at 11:25 AM.

  20. #20
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    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. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,549

    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.

    Thank you for your co-operation.

  22. #22
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    69

    Re: if criteria count or empty

    yes is working !!!thanks so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. function to count the average, check if not empty, then count
    By doudou in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-06-2019, 06:55 AM
  2. Count Blank and count based on criteria only Formula Allowed
    By Harmender in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2019, 12:26 PM
  3. [SOLVED] Count number of empty columns in a row since non-empty column
    By misterad in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-14-2018, 02:32 PM
  4. Replies: 2
    Last Post: 03-03-2018, 05:00 AM
  5. [SOLVED] Count number of empty cells and sum up too the first non-empty cell.
    By CraftyGamer in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-28-2017, 11:35 AM
  6. Replies: 10
    Last Post: 09-26-2015, 08:26 PM

Bookmarks

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