?
Solved

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

Posted on 2011-03-16
14
Medium Priority
?
311 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
Comment
Question by:singleton2787
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 33

Expert Comment

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

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

Cheers
Rob H
0
 

Author Comment

by:singleton2787
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

by:barry houdini
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Expert Comment

by:Rob Henson
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

by:barry houdini
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

by:singleton2787
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

by:singleton2787
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

by:barry houdini
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

by:barry houdini
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

by:singleton2787
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

by:singleton2787
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

by:
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

by:singleton2787
ID: 35150246
You are the man!
0
 

Author Closing Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

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

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

Join & Ask a Question