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

=COUNTIF(F4:F643,">0")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

=SUMPRODUCT(--(F4:F63>0),--(NOT(ISNUMBER(G4:G63))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Works great Bernie!

Thanks a million,

mikeburg