 # 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]

• Unless there was a pattern to the location of the "header SUMS" neither could I.... hence the UDF ## Participate now!

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