Reputation: 1
HI I need a macro to count the # of occurrances based on some conditions. here is sample of the data table:
ID Paydate # of payments
1 5/1/2011 3
1 5/1/2011 3
1 3/1/2011 2
1 2/1/2011 1
2 6/12/2011 3
2 5/12/2011 2
2 4/12/2011 1
3 4/25/2011 2
3 3/25/2011 1
I want to count the # of payments a ID has made up to that date (the 3rd column is what I need). For example for ID =1 and paydate = 5/1/2011 there have been 3 payments, for ID=1 and paydate = 3/1/2011 there are 2 payments. The macro should count the number of payments less or equal to the current date and does not increase the count if there are multiple IDs with the same date.
IF there is a way to do this with formulas that would be great but it seems too complex. Any help would be much appreciated.
Upvotes: 0
Views: 4183
Reputation: 8053
not really enough information to go on such as were do you get the date to look for and where do you get the id to look for. So if I make a bunch of assumptions I could write some VBA such as this. Also this is a bit long and could be broken down into another function to get the user responses
Option Explicit
Sub numberOfPayments()
On Error Resume Next
Dim iRow As Integer
Dim iCol As Integer
Dim dtDate As Date
Dim iID As Integer
Dim sResponse As String
Dim iNumberOfPayments As Integer
'initialize the variables
iNumberOfPayments = 0
iRow = 2
iCol = 1
'get the date
sResponse = InputBox("Calculate for what date? (M/D/YYYY)", "Get Payment Date")
'check to make sure its a valid date
If IsDate(sResponse) Then
'set date we are looking for
dtDate = CDate(sResponse)
Else
MsgBox "You must enter a valid date"
Exit Sub
End If
'reset the response
sResponse = ""
'get the ID
sResponse = InputBox("Calculate for what ID?", "Get Payment ID")
'set the ID to look for
iID = CInt(sResponse)
'if the conversion failed there will be an error
If Err.Number <> 0 Then
MsgBox "You must enter a valid ID"
Exit Sub
End If
'assumes you have data in each cell
Do While Cells(iRow, iCol).Value <> ""
'check the ID
If Cells(iRow, iCol).Value = iID Then
'this is done as not sure if the column is formatted as a date or just text
If IsDate(Cells(iRow, iCol + 1).Text) Then
If dtDate <= CDate(Cells(iRow, iCol + 1).Text) Then
'add the payments
iNumberOfPayments = iNumberOfPayments + Cells(iRow, iCol + 2).Value
End If
End If
End If
'move to the next row
iRow = iRow + 1
Loop
'inform them of the number of payments
MsgBox "there are " + Str(iNumberOfPayments) + " total payments."
End Sub
Upvotes: 0
Reputation: 14185
You don't need macros or vba.
=COUNTIFS(A:A,A2,B:B,"<=" & B2)
Put this in C2.
Upvotes: 3