COUNT ON NON-CONTINOUS CELLS

Hi,

Is there a way for me to get a count of cells (NOT in a RANGE) that have 
values greater than zero (0).

i.e.

A1=1
C3=0
D4=3
E2=0
E5=2

Result = 3

Thank you!



0
11/12/2009 12:12:43 PM
excel 39879 articles. 2 followers. Follow

3 Replies
658 Views

Similar Articles

[PageSpeed] 11

Hi Gerard
Try this =COUNT(IF(A1:C5>0,A1:A5))
This is an array formula you must press CTRL+SHIFT+ENTER to enter the formula. 
If successful in the Formula Bar  you can see the curly braces at both ends like 
{=COUNT(IF(A1:C5>0,A1:A5))}. do not type them in yourself.
HTH
John

"Gerard Sanchez" <geepeeoneREMOVEME@gmail.com> wrote in message 
news:OX4V5F5YKHA.5852@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Is there a way for me to get a count of cells (NOT in a RANGE) that have 
> values greater than zero (0).
>
> i.e.
>
> A1=1
> C3=0
> D4=3
> E2=0
> E5=2
>
> Result = 3
>
> Thank you!
>
>
> 

0
johnd1 (109)
11/12/2009 12:40:29 PM
Try this...

=INDEX(FREQUENCY((A1,C3,D4,E2,E5),0),2)

-- 
Biff
Microsoft Excel MVP


"Gerard Sanchez" <geepeeoneREMOVEME@gmail.com> wrote in message 
news:OX4V5F5YKHA.5852@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Is there a way for me to get a count of cells (NOT in a RANGE) that have 
> values greater than zero (0).
>
> i.e.
>
> A1=1
> C3=0
> D4=3
> E2=0
> E5=2
>
> Result = 3
>
> Thank you!
>
>
> 


0
biffinpitt (3172)
11/12/2009 6:07:54 PM
Here you are

=SUMPRODUCT(COUNTIF(INDIRECT({"A1","C3","D4","E2","E5"}),">0"))



-- 


Regards,


Peo Sjoblom


"Gerard Sanchez" <geepeeoneREMOVEME@gmail.com> wrote in message 
news:OX4V5F5YKHA.5852@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Is there a way for me to get a count of cells (NOT in a RANGE) that have 
> values greater than zero (0).
>
> i.e.
>
> A1=1
> C3=0
> D4=3
> E2=0
> E5=2
>
> Result = 3
>
> Thank you!
>
>
> 


0
11/12/2009 6:09:46 PM
Reply:

Similar Artilces:

How do I insert an image into a specific cell within a protected .
How do I insert an image into a specific cell within a protected worksheet. I have created an order form that is protected so users can only enter data into specific cells. However, I would like users to be able to insert a graphic file/image into a specific set of merged cells. Please help. Thanks, Scott I think I'd give them a macro to do it. The macro would ask for the filename, unprotect the sheet, add the graphic to the correct location, and reprotect the sheet. Option Explicit Sub testme02() Dim myPictureName As Variant Dim myPict As Picture Dim myRng As Range ...

Count cells with specific text between two dates?
I have the following formula =COUNTIF(Orders!D:D,"*Stood Down*") How do I modify it to count text between two dates which are in cells: Orders! H3 Orders! I3 Hi, You told us where the date criteria are but not where the dates are on the worksheet so I have assumed ORDERS!C:C In additiona, unless you have to cut backk the range from full columns to something more closely matching your dataset =SUMPRODUCT((Orders!C:C>=H3)*(Orders!C:C<=I3)*(ISNUMBER(SEARCH("stood down",Orders!D:D)))) -- Mike When competing hypotheses are otherwise equal, adop...

Locating Trendline Equation Factors in Cells
When adding a trendline on a chart, one option is to add the trendline equation to the chart. Is there a way to also populate off-chart cells with the various factors of the equation? For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06. The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06 value in cell A2. As values are added to the data matrix and the equation changes, the cell values would change accordingly. Hi Phil, Have a look at Bernard Liengme's examples of using worksheet formula to determine equation elements. http://www.stfx....

Relative/absolute cell references
I have a formula, like for instance = A1*A3 I like to copy this, relatively, that is updating it to =A2*A4 and so on. The data is supposed to be in A1, A3 or A2, A4, but if the user moves the data from A1 to B1 the fomula is updated to =B1*A3. Is there someway I can prevent that? So no matter where the user moves the data, the formula will remain unchanged? Jan Maybe =INDIRECT("A1")*INDIRECT("A3") -- HTH Bob Phillips "Jan Kronsell" <span@nospam.no> wrote in message news:%23qsTwdIVFHA.3540@TK2MSFTNGP15.phx.gbl... > I have a formula, like fo...

calculate percentage from cells in different worksheets
Hi, I have data on patients in excel file, with each patient's data placed on a separate sheet. For instance: PatientA has data on worksheet "PatientA" where cell A9 is hemoglobin data .... PatientZ has data on worksheet "PatientZ" where cell A9 is hemoglobin data I have a summary worksheet called "Summary". I need cell A9 in that sheet to give me the percentage of patients whose Hemoglobin value is between 10 and 12. Any help is appreciated. Thanks. see your other post -- Biff Microsoft Excel MVP "wissam" <wissam@discu...

Search Column for value in Cell & Replace
I'm sure this is very easy to do, but I'm having a lot of troubl figuring out how to do it How do I search a column for the value in a particular cell, and the insert the value into the current cell if found <table <tr A B <tr>1 jim james doesn't know what hes doin <tr>2 james monkeys throw po <tr>3 john jim is coo </table So I want to search all cells in column C for the non-case-sensitiv value in B1. If the value in B1 is found somewhere in Column...

cell formatting & Input box
Hi, the format of cell p_1 is "dd-mm-yy hh:mm" this procedure running inputbox: Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer Range("p_1").Select With ActiveCell.NumberFormat = "dd-MM-yy hh:mm" End With If Target.row = 3 And Target.Column = 2 And Range("p_1").Value = "" Or Range("p_1").Value = 0 Then _ : Range("p_1").Value = InputBox("Wpisz datę" & vbNewLine & vbNewLine & "Początkową" & vbNewLine & vbNewLine & "Delegacji PL:") Range("K_1&...

excel cells changing to "#VALUE!" whenever another file is opened
I have an excel file and formulae in two of the columns is changing to "#VALUE!", whenver a new file is opened and cells updated with data in the new file. All other cells are not effected and the only difference is, these two columns have Vookup function(to another sheet in the same file only).Also, pressing F2 in each cell and then pressing enter would calculate again. I have automatic calculation turned on. I could not understand why any change in a separate new file would mess up the formula in this file?Please help! I suspect that the VLOOKUP formula is returnin...

Junk Mail on Non-Outlook Client
I have IMF installed and it is working great; however I have one user with a Mac who is not able to have messages sent to his junk email folder since he is on a Mac running Entourage client. Is there any known way to get around this Thanks, Jerry Jerry - Have this user logon once with Outlook Web Access 2003 and set the Junk Mail filter on through that interface. This will create the extended server-side rule which moves the mail to Junk E-mail folder in conjunction with IMF. -- Evan Dodds Microsoft Exchange Support ****** Disclaimer ****** This posting is provided "AS IS" wit...

Can not see text in Excel when cell format is set to wrap text
When I select wrap text in format cell, or open an Excel file with wrap texted, the text is not visible. Tried changing row height & column width, but does not help. One way to make text "invisible" is to set the text font color the same as the background color. Make sure this is not happening. Make sure the text is not heavily indented Make sure there are not an excessive number of hard returns at the beginning of the text. -- Gary''s Student - gsnu200725 "Mark" wrote: > When I select wrap text in format cell, or open an Excel file with wrap > ...

counting rows with same values for multiple values
I have a list of 150 assets which are assigned to 20 or so depts. How can I count the number of assets per dept. In essence counting the number of times different values reoccur? For example Asset1 - dept1 asset2 - dept 1 asset3-dept2 asset4-dept2 asset5-dept3 dept1 has 2 assets dept2 has 2 assets dept3 has 1 asset Id like to avoid doing a COUNTIF and having to type each dept name Jon Viehe Wrote: > I have a list of 150 assets which are assigned to 20 or so depts. Ho > can I > count the number of assets per dept. In essence counting the number o > times > different val...

Selecting cells #2
When in a large spreadsheet, and selecting rows or columns from the middle, is it possible to find out how many rows or columns have been selected. ie, if I select names ending with the letters between D and R, is there something that can tell me how many I've selected without counting or pasting the range into another sheet? Thanks for your assistance, Mike "Mike", You don't say if it's whole rows and/or columns. You could try this UDF & code from John Walkenbach ... Sub AboutRangeSelection() Dim NumCols As Integer Dim NumRows As Long Dim NumBlo...

Use spreadsheet cells for global variable space?
I have two large arrays of integers that I would like to initialize when my workbook is opened. Since it seems too difficult (and maybe impossible) to make these arrays global to ALL of my VBA code modules, I thought of the following solution: How about I create one hidden sheet in my workbook and use the cells to simulate an array of data? If my data is stored in a hidden sheet, that sheet could be easily accessed by ANY of my VBA modules. Any comments or suggestions??? Hi Robert, I have always found it to be a very successful method. You can also set up your system ...

adding multiple checkboxes in a single cell
I want to add 2 checkboxes in one cell, similar to a yes/no checkbox but name one contract and one permanent so that the person entering the data chooses either or in that cell in order to identify if an employee is a permanent employee or not. Can this be done in excel? -- Thanks, Pat Not really, not the way I think you mean. But remember that one box has two conditions: checked and not checked. So you could simply put one box in the cell and set it up so that Checked means Permanent Employee and unchecked means not permanent employee (contractor/temp/etc). Get a checkbox, eithe...

Summing and count ?
How can I sum the last 3 columns or lines even if I add another line or column. Wk 1 2 WK2 5 WK3 4 WK4 3 Total 12 Following week will be WK1 2 WK2 5 WK3 4 WK4 3 WK5 2 Total 9 Assuming the total cell is in say A20 =SUM(OFFSET(A19,-2,0,3)) -- HTH RP (remove nothere from the email address if mailing direct) "Victor" <Victor@discussions.microsoft.com> wrote in message news:618E5D61-F0F9-44E8-BB36-1BE2B42B9F7B@microsoft.com... > How can I sum the last 3 columns or lines even if I add another line or column. > > Wk 1 2 > WK2 5 >...

Number of , in a cell
Is there a smart function to count the number of commas in a cell? Erik Hi =LEN(A1)-LEN(SUBSTITUTE(A1,",","") -- Regards Frank Kabel Frankfurt, Germany "Erik R" <herslov@hotmail.com> schrieb im Newsbeitrag news:oOrmd.17033$Km6.196919@news4.e.nsc.no... > Is there a smart function to count the number of commas in a cell? > > Erik > > ...

What formula will count the number of data < 0 in a column?
I have a list with values from -50 to + 50. I need a formula that will produce the percentage of values in that column less than zero. Hi =COUNTIF(A1:A100,"<0")/COUNT(A1:A100) and format as percentage -- Regards Frank Kabel Frankfurt, Germany "JackieB" <JackieB@discussions.microsoft.com> schrieb im Newsbeitrag news:30735B3F-40E9-4782-89F9-45709A5B9BCD@microsoft.com... > I have a list with values from -50 to + 50. I need a formula that will > produce the percentage of values in that column less than zero. > > If the range is A1:A4: =COUNTIF(A1...

Help..Conditionally formating a cell with numbers
Hi all, Is it possible to conditionally format a cell with numbers. I need a formula that will give a minimum value in a cell yet still allow a true value if it is over the minimum. Any help will be great, thanks in advance. Mick. Hi Mick, There is a feature in Excel called "Conditional Formatting" and it cannot change the value or the numberformatting of any cell. It can change the font, colors, and patterns in a cell. Remove the C.F. and it is revealed that no actual changes were made to the cell. Nor is Excel equipped to have a value in a cell but display a different value ba...

Backup Exec 10 brick-level backup fails on non-existent mailbox
A client of mine does both brick-level and information store backups nightly using Veritas Backup Exec. Last night's job failed because it tried to access a mailbox listed as a long string of numbers. This is not a valid mailbox, but I can't find a way to de-select it in the backup. Most of the posts I've read simply say not to do brick-level backups, but is there anything else I can do to prevent this from happening? Terry Hartup On 13 Nov 2006 12:07:01 -0800, "Terry Hartup" <terry.hartup@optigram.com> wrote: >A client of mine does both brick-level and info...

Copy Cells #2
Hi, I have 2 workbooks: # 1 Workbook.("TBD.xls").worksheet("Sheet1") #2 is a workbook that I open with this VBA macro: fName = Application.GetOpenFilename Workbooks.Open filename:=fName I also have this VBA Macro for fname blastrow = Cells(Rows.Count, "B").End(xlUp).Row isum = Application.WorksheetFunction.Sum(Range("b1:b10000")) Cells(blastrow + 2, "B").Value = isum My Question is: How do I copy the value of "Isum" in the Workbook.("TBD.xls").worksheet("Sheet1") in cell "F34" Thanks, Workbooks(&q...

Inserting the file path in a cell
I am using Excel 2003. I know how to insert the full path and file name in a footer, however, is there any way (possibly a function) to display the full path and file name in a cell? Yes. Use the =CELL("filename") function Have a good day -- Gary''s Student "netorius77" wrote: > I am using Excel 2003. I know how to insert the full path and file name in a > footer, however, is there any way (possibly a function) to display the full > path and file name in a cell? Too smart by far - thanks exactly what I wanted. "Gary''s Student"...

Applying number format from referenced cell
I have a cell that refers to another cell (linked worksheet) and I need to have the destination cell reflect the format of the reference cell, especially if I change the format of the reference to a custom format (<0.00). I need the cell value to remain a number for subsequent calculations. My template is already fairly large and will need to be used by novices so my solution needs to be as automatic as possible. Thanks. Formulas, including links to other workbooks, can only return values--not formats. I think copy|paste special|formats either manually or via a macro is the way to...

Counting number of rows #2
I have taken information from other sources and want to count how many rows I have without physically counting them as they have been inserted into the worksheet out of sequence. Is there a way to do this? -- Lynne Mawson Manchester Hi Lynne =COUNTA(A:A) will give you the number of cells in column A with data -- Regards Roger Govier "Lynne Mawson" <LynneMawson@discussions.microsoft.com> wrote in message news:58BCD3A6-8A8A-43E4-9C0B-8C1E9A4AAEA3@microsoft.com... > I have taken information from other sources and want to count how many > rows I > have without...

How do I merge two cells but keeping all contents in Excell?
I would like to merge two cells without losing the data ea: Column A= AB and Column B = 1234. End result want to achieve Column C = AB1234 Please help - Gys In column C: =A2&B2 (for row 2) This is a "short version" of concatenation, you can look up the CONCATENATE function, which I never use because I use the & method instead. :) ************ Anne Troy www.OfficeArticles.com "gysl" <gysl@discussions.microsoft.com> wrote in message news:7EBE232F-F5CA-4DCB-8435-45E3556C379F@microsoft.com... >I would like to merge two cells without losing the data ea: ...

Date count
I have a spreadsheet with dates in the cells. I want to create a formula that looks at column A and column B, if they are blank... then look at column C and if it is blank count it as 1. I tried something like this.. but it didn't give me the answer I was looking for! =SUMPRODUCT(--(A7&B7=" "),(COUNTA(C7,1))) It seems like if A7:B7 are blank....count that as 1 If that's true, try this: =--(COUNTA(A7:C7)=0) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tkacoo" <tkacoo@discussions....