YTD SUM

[Veeru]

Hi,

I am looking for a formula which gives me YTD sum of given scenarios.
Sample file attached, please assist on this.
Thank you

[JKwan]

=SUMPRODUCT(--(C2:C29=L2)*(A2:A29=M2)*(D2:I29))


=SUMPRODUCT(--(C2:C29=L6)*(A2:A29=M6)*(B2:B29=N6)*(D2:I29))

[Paul_Hossler]

1. Data is inconsistent -- Col C has Apr'18, Col L has Aprl'18

2. Date format is string and doesn't do the Less Than Or Equal test needed for a YTD calculation

I'd use a user defined function

Option Explicit

Function YTD(DB As Range, M As String, N As String, L As String) As Double
    Dim aryData As Variant
    Dim i As Long, j As Long
    Dim v As Variant
    Dim aryMonths As Variant
    Dim M1 As String, N1 As String, L1 As String
    Dim D1 As Double
    
    
    aryMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    aryData = Intersect(DB, DB.Parent.UsedRange).Value
        
    v = Split(M, "'")
    j = Application.WorksheetFunction.Match(v(0), aryMonths, 0)
    M1 = "20" & v(1) & "-" & Format(j + 1, "00")
    N1 = UCase(Trim(N))
    L1 = UCase(Trim(L))

    For i = LBound(aryData, 1) + 1 To UBound(aryData, 1)
        aryData(i, 1) = UCase(Trim(aryData(i, 1)))
        aryData(i, 2) = UCase(Trim(aryData(i, 2)))
        v = Split(aryData(i, 3), "'")
        j = Application.WorksheetFunction.Match(v(0), aryMonths, 0)
        aryData(i, 3) = "20" & v(1) & "-" & Format(j + 1, "00")
    Next i

    For i = LBound(aryData, 1) + 1 To UBound(aryData, 1)
        If Len(N1) > 0 And N1 <> aryData(i, 1) Then GoTo GetNext
        If Len(L1) > 0 And L1 <> aryData(i, 2) Then GoTo GetNext
        If aryData(i, 3) > M1 Then GoTo GetNext
        
        For j = 4 To 9
            D1 = D1 + aryData(i, j)
        Next j
GetNext:
    Next i

    YTD = D1
End Function

[Veeru]

Great thank you jKwan and Paul......i think we dont require macro for this.....formula will suffice.....

[Paul_Hossler]

You must be using a different definition of Year To Date than I'm used to

The formulas only return the month/location/name line that matches


Capture.JPG