Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-16
14
Medium Priority
?
313 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

610 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