+ Reply to Thread
Results 1 to 59 of 59

if count 0

  1. #1
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    if count 0

    hi, i have a formula at H1 which counting an event if only two numbers,what i need is: if in G:G apears any other number count 0!!!thanks guys

  2. #2
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    or alternative i would like a formula at F1 which looks the results of G:G and if any other number except 1 and 3 appeared must show 0 zero.thanks

  3. #3
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    =IFERROR(IF(A1:A25,"<>*2*"),(INT(MATCH(0,INDEX(--(A2:INDEX(A:A,COUNT(A:A))<>A1:INDEX(A:A,COUNT(A:A)-1)),N(IF(1,COUNT(A:A)-ROW(INDIRECT("1:"&COUNT(A:A)-1))))),)/2),INT(COUNT(A:A)/2))

    maybe something like this?but it does not work..

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: if count 0

    So if any number other than 1 and 3 appears you want 0 to appear?

    =IF(COUNTIF(G1:G14,"<>1",G1:G14,"<>3")<>0,0,"SOMETHING ELSE")

    You havent said what should appear if ONLY 1 and 3 appear (hence the SOMETHING ELSE)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    thanks special -k,i just copy but i cant paste it ,something with the formula maybe?

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: if count 0

    Sorry that should be COUNTIFS not COUNTIF so

    =IF(COUNTIFS(G1:G14,"<>1",G1:G14,"<>3")<>0,0,"SOMETHING ELSE")

    So when G1:G14 consists of 1s and 3s ONLY it displays SOMETHING ELSE
    but if G1:G14 has any number apart from 1 or 3, it displays 0

  7. #7
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    or better can we modified that formula with the same thought(if any number other than 1 and 3 appears count 0)
    =IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2))

    please!!!
    Last edited by louis128; 01-31-2019 at 07:00 AM.

  8. #8
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    =iferror(if(countif(a:a)<>,2,0"",(int(match(0,index(--(a2:index(a:a,count(a:a))<>a1:index(a:a,count(a:a)-1)),n(if(1,count(a:a)-row(indirect("1:"&count(a:a)-1))))),)/2),int(count(a:a)/2))
    Last edited by louis128; 01-31-2019 at 08:20 AM.

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: if count 0

    Why are you using a more complex totally different formula?
    What's wrong with the simple IF(COUNTIFS... as suggested?
    It outputs the correct value as defined in your description of the problem.

  10. #10
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    ,i put it at I1 but can you explain how can i mosified the formula at H1 with the same idea?please!!!!
    Last edited by louis128; 01-31-2019 at 08:20 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    Blanks count as 0 - try this:

    =IF(COUNTIFS(G1:G14,"<>1",G1:G14,"<>3",G1:G14,"<>")<>0,0,"SOMETHING ELSE")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: if count 0

    Repeating requests made by people trying to help you in other threads, as you appear to have forgotten, yet again!

    Don't just tell us 'it does not work'. If the result is not what you expect then the formula works, the wrong result is down to insufficient and / or inaccurate information from you when explaining your problem.

    Please remove any formulas that 'do not work' from your sample files and enter the EXACT results that you expect to see in the places that you want to see them.

  13. #13
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    iam sure is not difficult

    can we modified that formula with the same thought(if any number other than 1 and 3 appears count 0)
    =IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2))
    Last edited by louis128; 01-31-2019 at 08:20 AM.

  14. #14
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    thanks ali,it shows the somrthing else but see below what i need please
    Last edited by louis128; 01-31-2019 at 08:05 AM.

  15. #15
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    i want you to show me your Professionalism! show it..

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: if count 0

    The formula suggested is not wrong, the one you want to try and use is not suitable for the task.

  17. #17
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: if count 0

    Quote Originally Posted by louis128 View Post
    i want you to show me your Professionalism! show it..
    It has been shown, by using a more suitable formula, I selected COUNTIFS, and so did AliGW.
    We know what we're talking about.

  18. #18
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    =IFERROR(IF(G1:G)"<>2"),(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2))

    so if in G:G there is no number 2 ..count...

  19. #19
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    special-k you are from the best in the forum,the mistake is mine ,i confused you,i try to #18 to be more specific.and sorry for my english as is not my mother language becouse sometimes you can thing is rude


    i need to modified the formula at H1

  20. #20
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    Louis a 2 has not been mentioned anywhere in your explanations. You have been provided with formulas that work for the question you have asked. I also would have answered in exactly the same way as the solutions you have been provided with.

    You firstly have not said why the solutions do not work. They appear to work to me

    Secondly you have come up with some complicated formula and not said what you wish it to achieve. Maybe your question has changed slightly but as we are all are bereft of crystal balls and you have provided no explanation, it is hard to see how you will be provided with a solution. What do you really want to achieve?

  21. #21
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    sorry for my english,
    dear davsth, i have at H1 a formula that counts only two number(3 and 1 for example) event ,please see it in the attachement.what i need is to modified the formula ike that:if a 2 or any other number apeared in G:G , the formula to count 0zero

  22. #22
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    so do you wish to count the number of 3s and 1s and return this value as long as there are no other values in column G

    =IF(COUNTIFS(G1:G14,"<>1",G1:G14,"<>3",G1:G14,"<>")<>0,0,SUM(COUNTIF(G1:G14,{1,3})))

  23. #23
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    i dont want want to return, so Llets consider that at G:G there is a rndom generator of 3s and 1s and at H1 there is a formula which count a certain event,yes ?
    if the random generator at G:G make mistake and extract any other number than 3s and 1s (for example nuber 2) it must show at H1(formula) 0 zero!

  24. #24
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    That's what Ali gave you
    =IF(COUNTIFS(G1:G14,"<>1",G1:G14,"<>3",G1:G14,"<>")<>0,0,"SOMETHING ELSE")


    what do you want to appear in H1 when there is no problem eg the something else

  25. #25
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    can you modified the formula at H1?yes?have alook if you want to help! put 2 in G:G after the 3s and 1s the formula does not show zero 0
    Last edited by louis128; 01-31-2019 at 09:06 AM.

  26. #26
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    why is so difficult for you? what you dont unerstand?

  27. #27
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    Entered as an array (shift control enter)

    =IF(COUNTIFS(G:G,"<>1",G:G,"<>3",G:G,"<>")<>0,0,IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))

  28. #28
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    i try it ,the formula shows only zero 0

  29. #29
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    =IF(COUNTIF(G:G,"=2"),0,IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))


    IF(G:G,"=2"),0,IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))



    i try also that , nothing!

  30. #30
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    iam sure you,ali,special-k will find the solution!!

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    Quote Originally Posted by louis128 View Post
    i want you to show me your Professionalism! show it..
    I shall forgive you for this because I know that English is not your first language, but you should not be calling anyone's professionalism into question. Why is it so important to you to use a very long and overly complex formula that is unnecessary for the task you have described? What exactly do you mean by wanting to see my professionalism? What is it that you find unprofessional? If it is the simplicity of the formula, then that's irrelevant.

    Please remember that those who help here do so voluntarily and of their own goodwill. This is not a paid 'service': members will help if they are ready and able to do so. Do not take any help offered to you for granted and please also understand that if you are asking for help, then you are not the expert, and you need to be able to be guided.

    This thread is becoming very frustrating for those of us trying to make you rethink your requirements.

    I shall NOT forgive you for this, which is rude and completely unnecessary:

    why is so difficult for you? what you don't understand?
    Do NOT address other members in this way.
    Last edited by AliGW; 01-31-2019 at 09:27 AM.

  32. #32
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: if count 0

    I am lost, really, in discussion but I think when you combined Ali's & davsth's solution, you should get what you want....
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    with CSE of course
    Last edited by KOKOSEK; 01-31-2019 at 09:32 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  33. #33
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    please see my last attachement if is not clear ,i will quit!) forget ali formula, is working but that is different
    Last edited by louis128; 01-31-2019 at 09:35 AM.

  34. #34
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    CSE = enter the formula whilst holding down CTRL + SHIFT.

  35. #35
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: if count 0

    Quote Originally Posted by louis128 View Post
    yes!simple for you
    Is it some kind of sarcasm or is it really THIS what you were looking for?

  36. #36
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    no i ansewred to ali ,koko iam not that kind of stubid person

  37. #37
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: if count 0

    No problem.
    Anyway, when I've put combined formula which I've mentioned in my previous post in you latest attachment is still working correctly.
    Counting for 1s or 3s and showing 0 if any other number appear.

  38. #38
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    guys i put as you said Alis formula and working its shows zero when 2 apeared ,but then when 3s and1s continue the formula stops

    =IF(COUNTIFS(G1:G14,"<>1",G1:G14,"<>3",G1:G14,"<>")<>0,0,IFERROR(INT(MATCH(0,INDEX(--(G2:INDEX(G:G,COUNT(G:G))<>G1:INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))

  39. #39
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    we are very closed,you are the best team ,i love you! all!

  40. #40
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: if count 0

    I don't know what You mean as 'formula stops' but did You notice that range is described as G1:G14? So it stop working correctly, whatever You put into G15,G16......

  41. #41
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    you think i know ) i think the problem is at the second part of the formula(iferror) part

  42. #42
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: if count 0

    As I said imho formula from https://www.excelforum.com/excel-gen...ml#post5055730 works fine if you expand range as you need.

  43. #43
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    maybe we must put where is G2 aor G1 at the second part of the formula a LOOKUP formula to considered the last numbers in a row?

  44. #44
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    =IF(COUNTIFS(G:G,"<>1",G:G,"<>3",G:G,"<>")<>0,0,IFERROR(INT(MATCH(0,INDEX(--(LOOKUP(9E+99,1/(G1:G1000001<>""),G1:G100000),"":INDEX(G:G,COUNT(G:G))<>LOOKUP(9E+99,1/(G2:G1000001<>""),G1:G100000),"":INDEX(G:G,COUNT(G:G)-1)),N(IF(1,COUNT(G:G)-ROW(INDIRECT("1:"&COUNT(G:G)-1))))),)/2),INT(COUNT(G:G)/2)))

    try but something is not good:
    (
    Last edited by louis128; 01-31-2019 at 10:06 AM.

  45. #45
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: if count 0

    Louis, I'm going to try and simplify it a little. Look at this set of numbers and tell us what result you want from the formula.

    1,3,3,1,3,1

    And this line

    1,3,3,2,3,1

  46. #46
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    my dear Jason there is no result! believe me only counting!!,have a look care fully to the attachement ,the formula is is counting normally 3s and 1s if 2 apeared then shows zero which is correct but after that it stops counting the 3s and 1s, can you fix it?

  47. #47
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: if count 0

    Again, I ask, what result do you want to see? The number that the formula gives you is the result. If it is wrong, then we need to know what it should be.

    Does this work?

    =IF(OR(LOOKUP(1E+100,G:G)={1,3}),SUM(COUNTIF(G:G,{1,3})),)

    Or this

    =SUM(COUNTIF(G:G,{1,3}))

  48. #48
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    you are out of subject my friend.check for 2 minutes the workbook,and tell me can you fix it or not? if no ,dont ask questions

  49. #49
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    Louis - nobody can fix anything unless you answer their questions. Please do so without making comments that suggest that you think they aren't trying hard enough.

  50. #50
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    i ansewred.i have a formula that stops working, we all spend so much time and that jason asking silly question s,who is that jason??? what result he wants to know ,iam a magician or something? and he is showing up with some formulas out of subject and confused us!!i disprove him!!! i dont want him to apeared here,heis not proffesional at all!
    Last edited by louis128; 01-31-2019 at 10:33 AM.

  51. #51
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: if count 0

    You did not answer!

    If you think my questions are silly, you should try reading some of your own!

    I only ask questions to try and extract some useful information to try and solve your problem. Perhaps you should try a forum that is in your own language so that it easier for you to explain what you want. I give up wasting my time, you're beyond reasonable help.

  52. #52
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    you dont see that is a counting formula?stop ask question for my threads please

  53. #53
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    Rudeness and insults will not be tolerated. As he ignored an infraction in this regard, I have banned Louis for two days from participating here. If he continues to post rude and insulting messages when he returns, he will be banned permanently.

  54. #54
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: if count 0

    I was wondering if this is what he wants?
    =SUM(COUNTIF(G:G,{1,3}))
    I guess maybe I'll find out in a couple days.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  55. #55
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: if count 0

    He needs to consider his position - look at it as a time-out.

  56. #56
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: if count 0

    I dealt with Louis once on a different post and the language issue is a problem.

  57. #57
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    I agree with your actions Ali, but am guessing that he wants to the formula to apply to anything below the last none (1 or 3) or everything if this doesn't occur. But it is guessing and not at all clear. I also think his formula for this condition is probably too complicated but he wont say what it is meant to achieve!

  58. #58
    Banned User!
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    282

    Re: if count 0

    maybe something like that.thanks
    Last edited by louis128; 02-02-2019 at 05:29 PM.

  59. #59
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: if count 0

    Louis you don't appear to have any attachments now.

    You also still haven said what your current formula is meant to do

    You can identify the last value row that isn't 1 or 3 by the following entered as an array formula (entered with shift control enter)
    =MAX((G1:G20<>3)*(G1:G20<>1)*(G1:G20<>"")*(ROW(G1:G20)))
    .
    but it wold be much easier if you said what your current formula wants to do, it would be offset by the number of rows in the above

    I will not be putting any more effort into this until you explain what your current formula tries to do

+ 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. Replies: 2
    Last Post: 03-03-2018, 05:00 AM
  2. Replies: 1
    Last Post: 05-19-2017, 07:37 AM
  3. Replies: 17
    Last Post: 04-11-2016, 11:14 PM
  4. Macro count items in sheet2 and provide count in embedded label in sheet1
    By jaredmccullough in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-05-2015, 11:27 AM
  5. [SOLVED] Filtering macro, count number of rows and include count in last column.
    By Folshot in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2012, 07:17 AM
  6. Range.columns.count property returns wrong count after union operation
    By gopinan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2008, 04:48 AM
  7. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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