CODEWITHSUNDEEP
excelvba
josh han
josh han

Reputation: 1

Excel macro to count value base on condition

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

Answers (2)

Sorceri
Sorceri

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

enderland
enderland

Reputation: 14185

You don't need macros or vba.

=COUNTIFS(A:A,A2,B:B,"<=" & B2)

Put this in C2.

Upvotes: 3

Related Questions