Count Function
 

Hi,

Can someone please help/advise.

I am trying to create a formula that will check a range of cells and
IF MORE THAN ONE CONDITION IS TRUE, then return a COUNT result.

For example, the range would include columns A & B and contain 10
rows. A random number of the cells in Column A contain dates and a
random number of the cells in Column B contain numbers. The
remainding cells in both columns would be blank.

I would like a formula that calculates how many cells that have dates
in Column A ALSO have numbers in the SAME ROW in Column B.

Any advise would be gratefully appreciated.

Many thanks

Count Function
 

=Sumproduct(--isnumber(A1:A10),--isnumber(B1:B10))

a date passes the isnumber test.
--
Regards,
Tom Ogilvy


"Chris" wrote in message
om...
Hi,

Can someone please help/advise.

I am trying to create a formula that will check a range of cells and
IF MORE THAN ONE CONDITION IS TRUE, then return a COUNT result.

For example, the range would include columns A & B and contain 10
rows. A random number of the cells in Column A contain dates and a
random number of the cells in Column B contain numbers. The
remainding cells in both columns would be blank.

I would like a formula that calculates how many cells that have dates
in Column A ALSO have numbers in the SAME ROW in Column B.

Any advise would be gratefully appreciated.

Many thanks

Count Function
 

You can do it like this.

Sub mycounting()
Dim myCount As Integer, n As Integer, x As Integer
n = [set this to no. of rows in column A (I assume your range will have same
no. of rows in column B)]
x = [Set this to the first row number]
myCount = 0

Do While x <= n
If (Not IsEmpty(Cells(x, 1))) And IsDate(Cells(x, 1)) Then
If (Not IsEmpty(Cells(x, 2))) And IsNumeric(Cells(x, 2)) Then
myCount = myCount + 1
End If
End If
x = x + 1
Loop
MsgBox myCount
End Sub

Sharad
"Chris" wrote in message
om...
Hi,

Can someone please help/advise.

I am trying to create a formula that will check a range of cells and
IF MORE THAN ONE CONDITION IS TRUE, then return a COUNT result.

For example, the range would include columns A & B and contain 10
rows. A random number of the cells in Column A contain dates and a
random number of the cells in Column B contain numbers. The
remainding cells in both columns would be blank.

I would like a formula that calculates how many cells that have dates
in Column A ALSO have numbers in the SAME ROW in Column B.

Any advise would be gratefully appreciated.

Many thanks

Count Function
 

Sorry, you asked for formula and since this is programming group I answered
in vb code.

Sharad
"Sharad Naik" wrote in message
...
You can do it like this.

Sub mycounting()
Dim myCount As Integer, n As Integer, x As Integer
n = [set this to no. of rows in column A (I assume your range will have
same no. of rows in column B)]
x = [Set this to the first row number]
myCount = 0

Do While x <= n
If (Not IsEmpty(Cells(x, 1))) And IsDate(Cells(x, 1)) Then
If (Not IsEmpty(Cells(x, 2))) And IsNumeric(Cells(x, 2)) Then
myCount = myCount + 1
End If
End If
x = x + 1
Loop
MsgBox myCount
End Sub

Sharad
"Chris" wrote in message
om...
Hi,

Can someone please help/advise.

I am trying to create a formula that will check a range of cells and
IF MORE THAN ONE CONDITION IS TRUE, then return a COUNT result.

For example, the range would include columns A & B and contain 10
rows. A random number of the cells in Column A contain dates and a
random number of the cells in Column B contain numbers. The
remainding cells in both columns would be blank.

I would like a formula that calculates how many cells that have dates
in Column A ALSO have numbers in the SAME ROW in Column B.

Any advise would be gratefully appreciated.

Many thanks

Count Function
 

Hi Tom,

Unfortunately, sumproduct won't work in this particular case.

The key result I am looking for is a COUNT based on a certain condition.
As simply stated as possible, the condition is as follows:

If the date in any cell within the named range (e.g. within say Column
A) equals ****** (for example 01/01/2005), then look for a value in the
cell on the same row in say Column C. If a value is found (i.e. values
are present in both cells on the same row in Columns A & C), then COUNT
and return the result of 1 for each true instance.

Sorry if this is confusing. It is easy to see what I need when looking
at the s/s, but not so easy to explain very clearly.

Many thanks for any help you may have to offer.

Kind regards,

Chris


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Count Function
 

For any date:
=Sumproduct(--isnumber(A1:A10),--isnumber(C1:C10))


for a specific date

=Sumproduct(--(A1:A10=DateValue("01/01/2005")),--isnumber(C1:C10))

This does give a count for the conditions you describe.

--
Regards,
Tom Ogilvy


"Chris Goodhand" wrote in message
...
Hi Tom,

Unfortunately, sumproduct won't work in this particular case.

The key result I am looking for is a COUNT based on a certain condition.
As simply stated as possible, the condition is as follows:

If the date in any cell within the named range (e.g. within say Column
A) equals ****** (for example 01/01/2005), then look for a value in the
cell on the same row in say Column C. If a value is found (i.e. values
are present in both cells on the same row in Columns A & C), then COUNT
and return the result of 1 for each true instance.

Sorry if this is confusing. It is easy to see what I need when looking
at the s/s, but not so easy to explain very clearly.

Many thanks for any help you may have to offer.

Kind regards,

Chris


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!