Answered by:
Summing values with the same date
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
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/98aa705adf3f4a168b07aea14210c726.mp4If 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.basAndreas.
 Marked as answer by David_JunFeng Friday, April 1, 2016 9:25 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
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


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/98aa705adf3f4a168b07aea14210c726.mp4If 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.basAndreas.
 Marked as answer by David_JunFeng Friday, April 1, 2016 9:25 AM

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.

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.

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

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.