Hi,
I am looking for a formula which gives me YTD sum of given scenarios.
Sample file attached, please assist on this.
Thank you
Hi,
I am looking for a formula which gives me YTD sum of given scenarios.
Sample file attached, please assist on this.
Thank you
=SUMPRODUCT(--(C2:C29=L2)*(A2:A29=M2)*(D2:I29))
=SUMPRODUCT(--(C2:C29=L6)*(A2:A29=M6)*(B2:B29=N6)*(D2:I29))
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Great thank you jKwan and Paul......i think we dont require macro for this.....formula will suffice.....
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3