+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 15 of 31

count if not 0s

  1. #1
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    45,946

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    Re: count if not 0s

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

  5. #5
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    20,329

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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,196

    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
    20,329

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    Re: count if not 0s

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

  14. #14
    Forum Contributor
    Join Date
    09-04-2018
    Location
    cyprus
    MS-Off Ver
    2007
    Posts
    167

    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
    20,329

    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

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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