none
Summing values with the same date RRS feed

  • Question

  • Hi

    I have a worksheet with the following data:

    I would like to run an Excel VBA macro that produces two columns in a separate worksheet:

    The first column will contain the date from Column A and the second column will contain the sum of all the values in Column B which occur on that date. So for the above example, the separate worksheet would look like:

    It would be great to hear from anyone who can steer me in the right direction on how to approach this.

    Thanks


    • Edited by HarrierJet Thursday, March 24, 2016 5:55 AM
    Thursday, March 24, 2016 5:52 AM

Answers

  • The data is not in a format where i can use a pivot table, so i need to be able to use a VBA macro.

    Well, I can use a pivot table, have a look:
    https://dl.dropboxusercontent.com/u/35239054/Samples/98aa705a-df3f-4a16-8b07-aea14210c726.mp4

    If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

    Andreas.

    • Marked as answer by David_JunFeng Friday, April 1, 2016 9:25 AM
    Thursday, March 24, 2016 2:07 PM
  • Hi, HarrierJet

    According to your description, if you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need, use SUMIF function with VBA:
    Debug.Print Application.SumIf(Range("A1:A5"), "Criteria", Range("B1:B5"))
    For more information, click here to refer about WorksheetFunction.SumIfs Method (Excel)

    • Marked as answer by David_JunFeng Friday, April 1, 2016 9:26 AM
    Tuesday, March 29, 2016 5:13 AM

All replies

  • It would be great to hear from anyone who can steer me in the right direction on how to approach this.

    There is no need for a macro, use a pivot table.

    Andreas.

    • Proposed as answer by André Santo Thursday, March 24, 2016 11:23 AM
    • Unproposed as answer by HarrierJet Thursday, March 24, 2016 11:36 AM
    Thursday, March 24, 2016 11:03 AM
  • Hi Andreas

    The data is not in a format where i can use a pivot table, so i need to be able to use a VBA macro.

    Thursday, March 24, 2016 11:37 AM
  • The data is not in a format where i can use a pivot table, so i need to be able to use a VBA macro.

    Well, I can use a pivot table, have a look:
    https://dl.dropboxusercontent.com/u/35239054/Samples/98aa705a-df3f-4a16-8b07-aea14210c726.mp4

    If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

    Andreas.

    • Marked as answer by David_JunFeng Friday, April 1, 2016 9:25 AM
    Thursday, March 24, 2016 2:07 PM
  • Thanks for the pivot table video, i think i will be able to get a lot of use from pivot tables going forward.

    With my current situation, the issue is associated with the first row below my headers (see below). This row actually contains data (not blank as shown) which i don't want to use in the pivot table - not sure that is possible?

    In the meantime, i had some success writing a macro which i have included below. I am not a VBA expert, and the experts will probably cringe at the coding, but it does the job.

    Sub Summary()
    Dim Date1 As Date, Date2 As Date
    Dim i As Long, j As Long, x As Long, z As Long
    Dim TotalLength As Long, Length As Long
    Dim TotalTime As Double, Time As Double
    Sheets("Sheet1").Activate
    i = 19
    x = 0
    For j = 19 To 9999
    z = 0
    TotalLength = 0
    TotalTime = 0
    Do Until z = 1 Or Cells(i + 1, 1) = ""
    Length = Cells(i, 7).Value
    TotalLength = TotalLength + Length
    Time = Cells(i, 6).Value
    TotalTime = TotalTime + Time
    Date2 = Format(Cells(i + 1, 4), "dd/mm/yyyy")
    Date1 = Format(Cells(i, 4), "dd/mm/yyyy")
    z = Date2 - Date1
    i = i + 1
    Loop
    x = x + 1
    Sheets("Sheet2").Cells(x, 1) = Date1
    Sheets("Sheet2").Cells(x, 2) = TotalTime
    Sheets("Sheet2").Cells(x, 3) = TotalLength / 1852
    If Cells(i + 1, 1) = "" Then
    Exit Sub
    End If
    Next j
    End Sub
    Thanks for your help and i will definitely use pivot tables going forward where possible.

    Friday, March 25, 2016 3:09 AM
  • In the meantime, i had some success writing a macro which i have included below. I am not a VBA expert, and the experts will probably cringe at the coding, but it does the job.

    If you ask 10 programmers what is the VBA code to solve your problem, you'll get 10 different solutions. :-)

    If your code works it is not wrong.

    Anyway, please provide the real data on your next request.If I had written a macro for your original request, then it would not work for the data which we see now.

    If you change the data in between your requests, the code must be rewritten,that annoys me as a helper. I don't have so much time and desire to rewrite code again and again.

    Ask a clear question and you'll geta clear answer. .-)

    Andreas.

    Friday, March 25, 2016 9:17 AM
  • Hi, HarrierJet

    According to your description, if you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need, use SUMIF function with VBA:
    Debug.Print Application.SumIf(Range("A1:A5"), "Criteria", Range("B1:B5"))
    For more information, click here to refer about WorksheetFunction.SumIfs Method (Excel)

    • Marked as answer by David_JunFeng Friday, April 1, 2016 9:26 AM
    Tuesday, March 29, 2016 5:13 AM
  • Hi David

    Thanks for the alternative approach. I have attempted to get the SUMIF function to work for my example data below using the following code

    Dim Date1 as Date
    Dim SumTime as Double
    Date1 = Format(Cells(19, 4), "dd/mm/yyyy")
    SumTime = Application.WorksheetFunction.SumIf(Range("D19:D25"), Date1, Range("F19:F25"))
    msgbox SumTime

    The SumTime value returns as zero. I think this is because the values in the range "D19:D25" contain dates and times, so there is nothing equal to Date1 that can be summed.

    Is there a way to modify the above SUMIF function so that the values in the range "D19:D25" are only dates?

    I tried using the statement below prior to the SUMIF, but this only changes the format of the cell, so the actual values in the range "D19:D25" still don't equal Date1.

    Range("D19", "D25").NumberFormat = "dd/mm/yyyy"

    Any advice either yourself or anyone else reviewing this post can offer would be greatly appreciated.

    Thanks again.


    Sunday, April 3, 2016 1:15 PM