Count Function

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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
 
Sorry, you asked for formula and since this is programming group I answered
in vb code.

Sharad
Sharad Naik said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
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.
 
Back
Top