Count number of non-zeros in Non-contiguous range

  • If I have a set of data in a column as shown below, but I only want to count the number of nonzero cells with * marked to the right, what formula can I use or build?

    in this case, there are 15 nonzeros among 18 counted cells (since there are 3 cells contain 0.00%), so how can I display this result, i.e. 15?


    16.0% *
    6.5% *
    12.4% *

    17.1% *
    0.8% *
    2.2% *
    4.4% *
    4.5% *
    0.0% *

    0.0% *
    8.7% *
    0.0% *
    2.3% *
    2.0% *

    3.0% *
    4.5% *
    2.0% *

    6.3% *

  • Alternatively:


    entered with Ctrl / Shift / Return to create an array formula, but TJ's answer is probably better.



  • oh, sorry, I did not make myself clear on the question, the * I marked above is only for indication purpose. Please ignore the *.

    if that case, how to count of # of cells which contains non-zero value in a Non-contiguous range of data? thanks

  • I'm not sure what the criteria is for excluding the value that are <>0 but that you have not indicated with a *

    i.e. if these values were in A1:A26 (including the blank cells)

    =COUNTIF(A1:A26,">0") would return 19 , being the no. of non blanks that are > 0

    But how do you determine that you want to exclude the other 4 values ? Is is on a whim or is there a criteria ?

  • Aha... they're formulas :rock:

    So , you want to count the nonblank, nonzero, nonformula cells?



    Where COUNTFORMULA is a UDF (User Defined Function), the code for which is as follows

    [vba]Option Explicit

    Function COUNTFORMULA(rng As Range)
    Dim rCell As Range, cellCount As Double

    cellCount = 0
    For Each rCell In rng
    If rCell.HasFormula Then
    cellCount = cellCount + 1
    End If
    Next rCell

    COUNTFORMULA = cellCount

    End Function[/vba]

    Result for my EG of A1:A26 = 15 !! :guitar:

  • Following on from Will's discovery of certain cells being headers, I've been experimenting with creating a named range of non-consecutive cells (e.g. A2:A4, A7:A12, etc) and using this in a formula.

    However, while I can use Sum and Count on this range, I can't use SumIf or CountIf (at least not in XL97), so at the moment I can't see a better way than Will's.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!