none
Using Date to calculate the Sum For Items RRS feed

  • Question

  • hello All,

    Please I need some help with writing a macro or using excel formula to get the sum of the data. I am working with a large set of data. It runs up to more than column CC.

     I will be able to continue once I get the foundation

    I want to be able to get the total for each day  for each item in the result worksheet. I thought about using countifs but I have a lot of different response that I will need back

    Please how do I go about doing so using the Worksheet(working data) ?

    Pleassse I will really appreciate any help. Sorry for the long data sample

    thanks!!!!

    Worksheet(working data)
    DATE NAME RESPONSE BCOC detail O_PROGRAM BILL AMT type TYPE PGM
    10/1/2016 WEST   116 STREET YES 6183109 FUEL2 AOPFUEL $869.02 HLD FUEL AOP
    10/1/2016 AMSTERDAM AVENUE NO 6183109 ELEC AOPFUEL $993.48 HLD UTIL EAP
    10/1/2016 AMSTERDAM   AVENUE NO 6183109 FUEL2 AOPFUEL $1,234.42 HLD FUEL AOP
    10/1/2016 AMSTERDAM   AVENUE YES 6183109 FUEL2 AOPFUEL $551.97 HLD FUEL AOP
    8/26/2016 AMSTERDAM   AVENUE YES 6280423 ELEC EOP   UTILITY $167.10 HLD UTIL EAP
    8/27/2016 AMSTERDAM   AVENUE NO 6280109 FUEL2 EOP   UTILITY $2,629.27 HLD FUEL EAP
    8/8/2015 AMSTERDAM   AVENUE YES 6280109 FUEL2 EOP   UTILITY $1,400.68 HLD FUEL EAP
    10/1/2016 MANHATTAN   AVENUE YES 6280109 FUEL2 EOP   UTILITY $351.85 HLD FUEL EAP
    10/1/2016 MANHATTAN   AVENUE YES 6280109 FUEL2 EOP   UTILITY $488.41 HLD FUEL EAP
    8/7/2015 OLD   BROADWAY YES S002423 ELEC EOP   UTILITY $86.56 HLD UTIL AOP
    8/7/2015 WEST   116 STREET NO 6280109 FUEL2 EOP   UTILITY $1,252.10 HLD FUEL EAP
    8/25/2016 WEST   116 STREET YES 6280109 FUEL2 EOP   UTILITY $2,274.95 HLD FUEL EAP
    10/1/2016 WEST   116 STREET YES 6280109 FUEL2 EOP   UTILITY $1,535.80 HLD FUEL EAP
    10/2/2016 WEST   119 STREET NO 6280423 ELEC EOP   UTILITY $694.28 APR UTIL EAP
    9/20/2016 WEST   119 STREET YES 6183109 FUEL2 AOPFUEL $245.58 HLD FUEL AOP

    Worksheet(results)
    result 8/7/2015 10/1/2016 9/20/2016
    AMSTERDAM AVENUE      
    RESPONSE(YES)      
    RESPONSE(NO)      
    ELEC      
    FUEL2      
    EOP UTILITY      
    AOPFUEL      
    AOP      
    EAP      
    SUM_BILL_AMT      
    AVERAGE_BILL_AMT      


    • Edited by Chibut Thursday, October 13, 2016 3:04 PM
    Thursday, October 13, 2016 12:29 PM

All replies

  • Bump
    Thursday, October 13, 2016 3:05 PM
  • Hi Chibut,

    Please explain your requirements more clearly or definitely.

    what are summary keys, i.e. 
      1st key: Name
      2nd key: Date???

    If I could understand your wants, I will make macro(VBA code).  
    Regards,
    Ashidacchi

    Regards,
    Ashidacchi
    Friday, October 14, 2016 1:52 AM
  • hello Ashidacchi,

    Sorry for the vague explanation. The key will be the date. For an example, I want a sum or count of each item in the Worksheet(results) result column by date. it will be a daily event.

    for an example on 10/1/2016 it should have the following count/results :

    QUESTION  RESULT
    AMSTERDAM AVENUE 3
    YES 2
    RESPONSE(NO) 2
    ELEC 1
    FUEL2 4
    EOP UTILITY 1
    AOPFUEL 4
    SUM_BILL_AMT $5,184.69
    AVERAGE_BILL_AMT $1,036.94

    thanks!!!

    Hi Chibut,

    Please explain your requirements more clearly or definitely.

    what are summary keys, i.e. 
      1st key: Name
      2nd key: Date???

    If I could understand your wants, I will make macro(VBA code).  
    Regards,
    Ashidacchi

    Regards,
    Ashidacchi


    Friday, October 14, 2016 12:56 PM
  • Hi Chibut,

    I still cannot understand how "result" sheet would be. 
    Please describe fully on 10/1/2016.
    "WEST 116 STREET" is included in 10/1/2016... how is it expressed in Results? 

    Please share an Excel file via online storage, such as OneBox, Dropbox.
    Or send me it as an attached file .
      my mail address: (replace "**" to "@", "dot" to ".")
       ash dot m314 ** gmail dot com

    I want sheet "workingdata" includes three or four days data, sheet "result" is what you want to be. 

    Regards,
    Ashidacchi
    Saturday, October 15, 2016 12:40 AM
  • Hi,

    You could create a pivotetable then you could retrieve the value you want.

    For more information, please visit PivotTable Object (Excel)

       Set NewSheet = Sheets.Add
        NewSheet.Name = "test"
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table1", Version:=6).CreatePivotTable TableDestination:= _
            "test!R3C1", TableName:="PivotTable1", DefaultVersion:=6
        With Sheets("test").PivotTables("PivotTable1")
        .PivotFields("DATE").Orientation = xlRowField
            .PivotFields("DATE").Position = 1
        .AddDataField .PivotFields("RESPONSE"), "Count of RESPONSE", xlCount
        .AddDataField .PivotFields("detail"), "Count of detail", xlCount
        .AddDataField .PivotFields("O_PROGRAM"), "Count of O_PROGRAM", xlCount
        .AddDataField .PivotFields("BILL AMT"), "Sum of BILL AMT", xlSum
        .AddDataField .PivotFields("BILL AMT"), "Average of BILL AMT", xlAverage
        .PivotFields("NAME").Orientation = xlPageField
            .PivotFields("NAME").Position = 1
        .PivotFields("RESPONSE").Orientation = xlPageField
            .PivotFields("RESPONSE").Position = 1
        .PivotFields("detail").Orientation = xlPageField
            .PivotFields("detail").Position = 1
        .PivotFields("O_PROGRAM").Orientation = xlPageField
            .PivotFields("O_PROGRAM").Position = 1
        End With


    Monday, October 17, 2016 9:21 AM
    Moderator
  • Thanks for your response. I really appreciate it. I wanted to go  the pivot table route but she does not want a pivot table.

    She does not want to be clicking drop down. She wants the summary of the value in one view.

    Monday, October 17, 2016 12:30 PM
  • Hi,

    You could create your own function to count the number.

    E.g.
    Function testFunction(srhDate As Range, srhValue As Range, rng As Range) As Integer
    'The formula is like =testFunction(A2,B3,Table1[NAME])
    Dim cell As Range
    For Each cell In rng
    'column NAME is Offset(0, -1)
    'column RESPONSE should be Offset(0, -2)
    If cell.Offset(0, -1).Value = srhDate.Value Then
    If cell.Value = srhValue Then
    testFunction = testFunction + 1
    End If
    End If
    Next cell
    End Function

    You could create a pivottable then retrieve the value.

    Sub Demo()
    Application.ScreenUpdating = False
        Set NewSheet = Sheets.Add
        NewSheet.Name = "test"
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Table1", Version:=6).CreatePivotTable TableDestination:= _
            "test!R3C1", TableName:="PivotTable1", DefaultVersion:=6
        With Sheets("test").PivotTables("PivotTable1")
        .PivotFields("DATE").Orientation = xlRowField
            .PivotFields("DATE").Position = 1
        .AddDataField .PivotFields("NAME"), "Count of NAME", xlCount
        .AddDataField .PivotFields("RESPONSE"), "Count of RESPONSE", xlCount
        .AddDataField .PivotFields("detail"), "Count of detail", xlCount
        .AddDataField .PivotFields("O_PROGRAM"), "Count of O_PROGRAM", xlCount
        .AddDataField .PivotFields("BILL AMT"), "Sum of BILL AMT", xlSum
        .AddDataField .PivotFields("BILL AMT"), "Average of BILL AMT", xlAverage
        .PivotFields("NAME").Orientation = xlPageField
            .PivotFields("NAME").Position = 1
        .PivotFields("RESPONSE").Orientation = xlPageField
            .PivotFields("RESPONSE").Position = 1
        .PivotFields("detail").Orientation = xlPageField
            .PivotFields("detail").Position = 1
        .PivotFields("O_PROGRAM").Orientation = xlPageField
            .PivotFields("O_PROGRAM").Position = 1
        End With
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("A18").Value = "Result"
    ws.Range("A19").Value = "AMSTERDAM AVENUE"
    ws.Range("A20").Value = "RESPONSE(YES)"
    ws.Range("A21").Value = "RESPONSE(NO)"
    ws.Range("A22").Value = "ELEC"
    ws.Range("A23").Value = "FUEL2"
    ws.Range("A24").Value = "EOP UTILITY"
    ws.Range("A25").Value = "AOPFUEL"
    ws.Range("A26").Value = "SUM_BILL_AMT"
    ws.Range("A27").Value = "AVERAGE_BILL_AMT"
    
    Set NewSheet = Sheets("test")
    Set pvtTable = Sheets("test").PivotTables("PivotTable1")
    For i = 1 To pvtTable.PivotFields(1).PivotItems.Count
    Sheets("Sheet1").Cells(18, i + 1).Value = pvtTable.PivotFields(1).PivotItems(i).Value
    Next i
    
    NewSheet.PivotTables("PivotTable1").ClearAllFilters
    Dim rng As Range
    Dim cell As Range
    Sheets("Sheet1").Select
    Set rng = Sheets("Sheet1").Range(Cells(18, 2), Cells(18, pvtTable.PivotFields(1).PivotItems.Count + 1))
    
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(8, 0).Value = "" Then
            cell.Offset(8, 0).Value = pgRange.Cells(j, 6).Value
            End If
            If cell.Offset(9, 0).Value = "" Then
            cell.Offset(9, 0).Value = pgRange.Cells(j, 7).Value
            End If
        End If
        Next cell
    Next j
    
    NewSheet.PivotTables("PivotTable1").PivotFields("NAME").ClearAllFilters
    NewSheet.PivotTables("PivotTable1").PivotFields("NAME").CurrentPage = "AMSTERDAM AVENUE"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(1, 0).Value = "" Then
            cell.Offset(1, 0).Value = pgRange.Cells(j, 2).Value
            End If
        End If
        Next cell
    Next j
    NewSheet.PivotTables("PivotTable1").PivotFields("NAME").ClearAllFilters
    
    NewSheet.PivotTables("PivotTable1").PivotFields("RESPONSE").CurrentPage = "YES"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(2, 0).Value = "" Then
            cell.Offset(2, 0).Value = pgRange.Cells(j, 3).Value
            End If
        End If
        Next cell
    Next j
    
    NewSheet.PivotTables("PivotTable1").PivotFields("RESPONSE").CurrentPage = "NO"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(3, 0).Value = "" Then
            cell.Offset(3, 0).Value = pgRange.Cells(j, 3).Value
            End If
        End If
        Next cell
    Next j
    NewSheet.PivotTables("PivotTable1").PivotFields("RESPONSE").ClearAllFilters
    
    NewSheet.PivotTables("PivotTable1").PivotFields("detail").CurrentPage = "ELEC"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(4, 0).Value = "" Then
            cell.Offset(4, 0).Value = pgRange.Cells(j, 4).Value
            End If
        End If
        Next cell
    Next j
    
    NewSheet.PivotTables("PivotTable1").PivotFields("detail").CurrentPage = "FUEL2"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(5, 0).Value = "" Then
            cell.Offset(5, 0).Value = pgRange.Cells(j, 4).Value
            End If
        End If
        Next cell
    Next j
    NewSheet.PivotTables("PivotTable1").PivotFields("detail").ClearAllFilters
    
    NewSheet.PivotTables("PivotTable1").PivotFields("O_PROGRAM").CurrentPage = "AOPFUEL"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(6, 0).Value = "" Then
            cell.Offset(6, 0).Value = pgRange.Cells(j, 5).Value
            End If
        End If
        Next cell
    Next j
    
    NewSheet.PivotTables("PivotTable1").PivotFields("O_PROGRAM").CurrentPage = "EOP   UTILITY"
    Set pgRange = pvtTable.PivotFields(1).DataRange
    For j = 1 To pgRange.Count
        For Each cell In rng
        If cell.Value = pgRange.Cells(j, 1).Value Then
            If cell.Offset(7, 0).Value = "" Then
            cell.Offset(7, 0).Value = pgRange.Cells(j, 5).Value
            End If
        End If
        Next cell
    Next j
    NewSheet.PivotTables("PivotTable1").PivotFields("O_PROGRAM").ClearAllFilters
    NewSheet.PivotTables("PivotTable1").ClearAllFilters
    Application.DisplayAlerts = False
    NewSheet.Delete
    Application.DisplayAlerts = True
    'For Each cell In Sheets("Sheet1").Range("B19:I25")
    'If cell.Value = "" Then cell.Value = 0
    'Next cell
    Application.ScreenUpdating = True
    End Sub
    
    

    The result:

    Notice there are different spaces in the string of your sample and they become different value. 

    Tuesday, October 18, 2016 3:58 PM
    Moderator
  • hi Celeste,

    Just saw this. I am going to step through it and let you know if it works. Thanks soooooo much for taking out the time!!!!!

    I really appreciate it a lot!!!

    Thursday, October 20, 2016 1:02 PM
  • Hi,

    Does the code work for you?

    If it doesn’t work, you could post the issue here, we could help check what causes and how to modify.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 28, 2016 11:34 AM
    Moderator