# Count the number of cells with a numeric value greater then 0

How would I count the number of cells in a column that had a numeric value greater than 0, but disregard any other values (N/A, #Value, etc.). I don't want a SUM of the numbers, just a COUNT of the cells that have a numeric value greater than 0.
###### Who is Participating?

x

Commented:
Like I said, there are 2 reasons why that won't work. The 2 as the first argument of SUBTOTAL means that you get a COUNT type function, COUNT counts numbers only (not errors)....so firstly you need to switch that to COUNTA, i.e. with a 3.....also you can't use A1=#N/A or similar because that formula returns an error so you need to use ISNA function, in your formula that would be

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I2,ROW(I2:I4734)-ROW(I2),0)),ISNA(I2:I4734)+0)

That simply counts #N/A errors that are visible
0

Finance AnalystCommented:
You should be able to use the COUNTIF function.

=COUNTIF(A1:A10,">0")

Cheers
Rob H
0

Author Commented:
I tried that..and it does work. But this is counting in a filtered column. That seems to not work correctly?
0

Commented:
If you want to count numeric values > 0 and include only the visible values after filtering then try this "array formula"

=SUM(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),IF(A2:A100>0,1)))

confirmed with CTRL+SHIFT+ENTER

assumes a range of A2:A100, change as required

Note: a simpler formula, i.e. =SUBTOTAL(2,A2:A100) will only count visible numbers and also ignore errors.....but it also counts negative numbers and zero........

see attached example - formula in D1

regards, barry
26890701.xlsx
0

Finance AnalystCommented:
Would the DCOUNT function work for you?

You could specify the additional filter criteria in the criteria range along with the value >0 criteria.

Cheers
Rob H
0

Commented:
......One more option would be to include a helper column which returns 1 only if the relevant cell in column A is a number and isn't filtered out, e.g. in my sample attached above you can use this formula in C2 copied down

=SUBTOTAL(2,A2)

Now for a count of visiable >0 numbers in A2:A100 you can use just

=COUNTIFS(C2:C100,1,A2:A100,">0")

regards, barry
0

Author Commented:
One last question, then you get 1,000,000 pts brother!
What about counting the #N/A? I tried this:
=SUM(IF(SUBTOTAL(2,OFFSET(I2,ROW(I2:I4734)-ROW(I2),0)),IF(I2:I4734="#N/A",1)))
0

Author Commented:
Also, this seemed to work better in the helper col:
=COUNTIF(L2:L4734,">0") <== this returned a value
0

Commented:
For a million......!

You need to use 3 in SUBTOTAL rather than 2 (3 like COUNTA counts error values) and also ISNA function, i.e.

=SUM(IF(SUBTOTAL(3,OFFSET(I2,ROW(I2:I4734)-ROW(I2),0)),IF(ISNA(I2:I4734),1)))

confirmed with CTRL+SHIFT+ENTER

....or for that version you can use a non-array formula with SUMPRODUCT i.e. this formula entered normally

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I2,ROW(I2:I4734)-ROW(I2),0)),ISNA(I2:I4734)+0)

Note that if you upgrade to Excel 2010 (or if you already have it) then you can do the original count more easily with AGGREGATE function......
0

Commented:
...the idea of the helper column is to only get a 1 when the row isn't hidden by the filtering, COUNTIF doesn't make a distinction between filtered and unfiltered values. The SUBTOTAL function I suggested will also give a 1 for zero and negative values....but those are then ignored in the COUNTIFS function for the count.......

which version of Exel are you using?

barry
0

Author Commented:
2007... let me play with this a little more. But here is the row values

I - DATE and some #N/As
J - the same
K - the same

When I apply a filter (trying to count the number of accounts by a date range), I just need to get a total of the filtered rows, make sense? The total number is 4734 acounts, but I need to find out how many were entered in 1/1/2011 through 6/1/2011 (by using a filter, I would think) and then just count them up at the bottom.
0

Author Commented:
Why doesn't this formula count the #N/A?
=SUM(IF(SUBTOTAL(2,OFFSET(I2,ROW(I2:I4734)-ROW(I2),0)),IF(I2:I4734"#N/A",1)))
0

Author Commented:
You are the man!
0

Author Commented:
1,000,000 pts awarded and a free exotic Italian sports car
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.