| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of cells in a range with amount 0
What would be the simplest code to get a count of the number of cells in a range F4:F63 that contains an amount greater then zero? Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
=COUNTIF(F4:F643,"0")
-- HTH RP (remove nothere from the email address if mailing direct) "mikeburg" wrote in message ... What would be the simplest code to get a count of the number of cells in a range F4:F63 that contains an amount greater then zero? Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
Hi,
Try =COUNTIF(F4:F63,"0") HTH, David Jessop "mikeburg" wrote: What would be the simplest code to get a count of the number of cells in a range F4:F63 that contains an amount greater then zero? Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
Mike,
Dim i As Integer i = Application.WorksheetFunction.CountIf(Worksheets(" Sheet1").Range("F4:F63"), "0") MsgBox i or you can leave out the worksheetfunction part, and the variable: MsgBox Application.CountIf(Worksheets("Sheet1").Range("F4 :F63"), "0") HTH, Bernie MS Excel MVP "mikeburg" wrote in message ... What would be the simplest code to get a count of the number of cells in a range F4:F63 that contains an amount greater then zero? Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
Works great, Bernie, for one worksheet. However, I have another that I need to count, if range F4:F63 contain an amount greater than zero AND if the corresponding row of G4:G6 contains a text string. Any ideas? In other words I need a count of the rows whose cells F4:F63 ar greater than zero AND whose cells G4:g63 contains text too. I am new to VBA, so I really appreciate everyone helping me out. learn a lot from this! mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?threadid=38645 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
=SUMPRODUCT(--(F4:F630),--(NOT(ISNUMBER(G4:G63))))
-- HTH RP (remove nothere from the email address if mailing direct) "mikeburg" wrote in message ... Works great, Bernie, for one worksheet. However, I have another that I need to count, if range F4:F63 contains an amount greater than zero AND if the corresponding row of G4:G63 contains a text string. Any ideas? In other words I need a count of the rows whose cells F4:F63 are greater than zero AND whose cells G4:g63 contains text too. I am new to VBA, so I really appreciate everyone helping me out. I learn a lot from this! mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
Mike,
In VBA, you could use Dim i As Integer i = Application.Evaluate("SumProduct((F4:F63 0)* IsText(G4:G63))") MsgBox i HTH, Bernie MS Excel MVP "mikeburg" wrote in message ... Works great, Bernie, for one worksheet. However, I have another that I need to count, if range F4:F63 contains an amount greater than zero AND if the corresponding row of G4:G63 contains a text string. Any ideas? In other words I need a count of the rows whose cells F4:F63 are greater than zero AND whose cells G4:g63 contains text too. I am new to VBA, so I really appreciate everyone helping me out. I learn a lot from this! mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of cells in a range with amount 0
Works great Bernie! Thanks a million, mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=386450 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count a number in a range with cells containing more than one numb | Excel Worksheet Functions | |||
| Count the number of cells containing a date within a range | Excel Discussion (Misc queries) | |||
| Count cells in column that contain number in a range | Excel Worksheet Functions | |||
| how do I count the number of gray-shaded cells within a range? | Excel Worksheet Functions | |||
| count number of cells in range showing between 320 and 345 | Excel Worksheet Functions | |||
![mikeburg[_6_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
