| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count non-zero cells
Excel XP & Win XP
I have a sheet with zero values suppressed (Tools - Options - View tab - Uncheck Zero values). I have a range, say F13:F16. Each cell has a formula that produces a number. The result in one cell is zero so that cell appears blank. I want to count the number of non-zero cells in the range. The worksheet formula: CountIf(F13:F16,"<0") produces the correct answer, 3. I need to do this in VBA so I use: Range("H7").Value = Application.CountIf(Range("F13:F17"), "<0") This produces a 4. What is the correct syntax to produce a 3? Thanks for your time. Otto |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count non-zero cells
You have different ranges in your formula (F13:F16) & (F13:F17)
using the same range in both I get the same answer. Mike "Otto Moehrbach" wrote: Excel XP & Win XP I have a sheet with zero values suppressed (Tools - Options - View tab - Uncheck Zero values). I have a range, say F13:F16. Each cell has a formula that produces a number. The result in one cell is zero so that cell appears blank. I want to count the number of non-zero cells in the range. The worksheet formula: CountIf(F13:F16,"<0") produces the correct answer, 3. I need to do this in VBA so I use: Range("H7").Value = Application.CountIf(Range("F13:F17"), "<0") This produces a 4. What is the correct syntax to produce a 3? Thanks for your time. Otto |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count non-zero cells
Hi Otto
Unless it was a typo when posting, you are looking at 2 different size ranges F13:F16 with formula F13:F17 in code This may well account for the different result. -- Regards Roger Govier "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a sheet with zero values suppressed (Tools - Options - View tab - Uncheck Zero values). I have a range, say F13:F16. Each cell has a formula that produces a number. The result in one cell is zero so that cell appears blank. I want to count the number of non-zero cells in the range. The worksheet formula: CountIf(F13:F16,"<0") produces the correct answer, 3. I need to do this in VBA so I use: Range("H7").Value = Application.CountIf(Range("F13:F17"), "<0") This produces a 4. What is the correct syntax to produce a 3? Thanks for your time. Otto |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count non-zero cells
Mike, Roger
You're absolutely right. A typo. Thanks. Otto "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a sheet with zero values suppressed (Tools - Options - View tab - Uncheck Zero values). I have a range, say F13:F16. Each cell has a formula that produces a number. The result in one cell is zero so that cell appears blank. I want to count the number of non-zero cells in the range. The worksheet formula: CountIf(F13:F16,"<0") produces the correct answer, 3. I need to do this in VBA so I use: Range("H7").Value = Application.CountIf(Range("F13:F17"), "<0") This produces a 4. What is the correct syntax to produce a 3? Thanks for your time. Otto |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to count#cells w/= value in other column and not count blank c | Excel Worksheet Functions | |||
| count cells, then reset count when value in another cell changes | Excel Worksheet Functions | |||
| How do I count cells with text but ignore cells with spaces? | Excel Discussion (Misc queries) | |||
| Count Empty Cells in Range After Cells with Data | Excel Programming | |||
| Using the COUNT funciton to count cells with values | Excel Programming | |||
Linear Mode
