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?


    thanks


    34.9%
    16.0% *
    6.5% *
    12.4% *


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


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


    9.5%
    3.0% *
    4.5% *
    2.0% *


    6.3% *

  • Alternatively:


    =SUM(IF(A1:A27<>0,IF(B1:B27="*",1,0)))


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


    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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?


    OK....


    =SUMPRODUCT((A1:A26<>0)*1)-COUNTFORMULA(A1:A26)


    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
    Application.Volatile


    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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

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