Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

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

Posted on 2011-03-16
Medium Priority
316 Views
Last Modified: 2012-05-11
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.
0
Question by:singleton2787
• 7
• 5
• 2
14 Comments

LVL 34

Expert Comment

ID: 35147893
You should be able to use the COUNTIF function.

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

Cheers
Rob H
0

Author Comment

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

LVL 50

Expert Comment

ID: 35148300
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

LVL 34

Expert Comment

ID: 35148363
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

LVL 50

Expert Comment

ID: 35148402
......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 Comment

ID: 35149042
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 Comment

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

LVL 50

Expert Comment

ID: 35149184
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

LVL 50

Expert Comment

ID: 35149225
...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 Comment

ID: 35149387
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 Comment

ID: 35149492
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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35149670
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

Author Comment

ID: 35150246
You are the man!
0

Author Closing Comment

ID: 35150260
1,000,000 pts awarded and a free exotic Italian sports car
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month11 days, 7 hours left to enroll

#### 564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.