Count Function

[Chris]

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

 

[Tom Ogilvy]

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

a date passes the isnumber test.

 

[Sharad Naik]

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

 

[Sharad Naik]

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

Sharad

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

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

 

[Chris Goodhand]

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

 

[Tom Ogilvy]

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.