+ Reply to Thread
Results 1 to 31 of 31

count if not 0s

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

    count if not 0s

    Hi to all, i would like a formula which counts the delays of 0(zero).i hope is easy!thanks so much!!

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

    Re: count if not 0s

    i attached an example here!

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,544

    Re: count if not 0s

    Can you explain how you arrive at your sample answers?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

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

    Re: count if not 0s

    i would like to cound delays in cell D2 please!!

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

    Re: count if not 0s

    what i need is a formula which checks the numbers at C:C and countsthe dalays of 0 zero.
    thank you!
    Last edited by louis128; 01-12-2019 at 04:47 AM.

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

    Re: count if not 0s

    is it posssible,to count?

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    This is not clear. I do not know if you want a series of values... or just one which gives the result to the last row in the column.

    In C4, an array formula:
    =COUNTIF(INDEX($B$4:B4,MAX(($B$4:B4=0)*MATCH(ROW($B$4:B4),ROW($B$4:B4)))):B4,"<>0")

    copied down.

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



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

    Re: count if not 0s

    is working!! But in order to go down ,it cant track C:C and just write the last result on D4?

    Thanks clenn!!!!

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

    Re: count if not 0s

    here is something similar please! And if i want to count how many times a number(any number) did not apear at D4(D4 is just lets say a counter of any number,except 0)
    can D4 get a memory?)

  10. #10
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,801

    Re: count if not 0s

    In "D3"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Ensure press shift+ctrl+enter


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    Try this alternative:

    =COUNTIF(INDEX(Range,MAX((Range=0)*MATCH(ROW(Range),ROW(Range)))):INDEX(B:B,MAX(IF(Range<>"",ROW(Range),""))),"<>0")

    again, an array formula, an also a named Range (CTRL-F3 to view/edit)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-12-2019 at 05:31 AM.

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

    Re: count if not 0s

    yes is grat , but please see the new example!
    At this stage we are interesting in C3 and F4 cells
    thanksClenn!

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

    Re: count if not 0s

    i think is difficult cause C3 has no memory ,yes?

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

    Re: count if not 0s

    actually ,i need a formula which counts how many times a number did not apeared in C3. so in this way it would count 0s , yes?

  15. #15
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    Another array formula:

    =COUNTIF(INDEX(Range,MAX((Range<>0)*MATCH(ROW(Range),ROW(Range)))):INDEX(C:C,MAX(IF(Range<>"",ROW(Range),""))),0)
    Attached Files Attached Files

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

    Re: count if not 0s

    super ! but my clenn i would like F4 to look only on E4(counter) just the counter and check how many times a number diid not apeared and counts like the formula youmade!
    Last edited by louis128; 01-12-2019 at 06:16 AM.

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

    Re: count if not 0s

    or maybe can we create for the counter E4 a range? so is like first step is the range B:B ,then the counter E4, then to create a 'new range' for the counter and F4 to look on 'new range'?
    Last edited by louis128; 01-12-2019 at 06:18 AM.

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

    Re: count if not 0s

    can we create an range of counter? i mean whick shows the counting results of E4 , please!

  19. #19
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    No. I do not understand what you want.

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

    Re: count if not 0s

    dear clenn ,I want in Q4 amodification of the formula like you made, to count a number did not apeared!, so it means alternative it counting how many zero apeared, yes? i attached you the exact example, sorry if i was not clear before!

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

    Re: count if not 0s

    I want in Q4 amodification of the formula like you made, to count how many times a number(any number except 0) did not apeared in P2.

  22. #22
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    I do not understand your English. Please show me your expected results for two or three example sets of data.

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

    Re: count if not 0s

    thanks! i will try to explain better. so atthe last work book i attached, i have a formula which counts the event 1 2 in O:O, so if 1 2 comes counts 1, if 1 2 1 2 1 2 comes counts 3 but if any other event comes like 222,or 112222 counts 0 zero.what i need is a 'history of counts.



    please see attachement example 2. thanks!

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

    Re: count if not 0s

    i attached another example of 'history'.

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

    Re: count if not 0s

    please have a look to that example!!thanks

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

    Re: count if not 0s

    is like

    extract the results of the array formula(or counter)!!!

  27. #27
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: count if not 0s

    Your examples do not help. Post one file, not 3. Remove EVERYTHING except the relevant data. Your sheets are full of rows of numbers and I do not have the patience, the time, or the enthusiasm to figure it all out. You MUST tell me what results you expect to see. There is no point in repeating the same words, as they do not make sense to me.

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

    Re: count if not 0s

    please check that one

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

    Re: count if not 0s

    and last you put the extract results where was like i show here!thanks alot

  30. #30
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,801

    Re: count if not 0s

    Mr. Louis, as per your last so many example, all necessary result's, solved out. This is a new one, kindly closed this thread, & post new one.

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

    Re: count if not 0s

    ok.thanks.

+ 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. 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
  6. Replies: 18
    Last Post: 09-06-2005, 06:05 AM
  7. [SOLVED] 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, 03: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