Solved

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

Posted on 2011-03-16
14
306 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
  • 7
  • 5
  • 2
14 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
You should be able to use the COUNTIF function.

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

Cheers
Rob H
0
 

Author Comment

by:singleton2787
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
......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
Comment Utility
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
Comment Utility
Also, this seemed to work better in the helper col:
=COUNTIF(L2:L4734,">0") <== this returned a value
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
Comment Utility
...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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
You are the man!
0
 

Author Closing Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now