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


22177