Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Sorting monthly sales after checking date...

    Hi,

    Now quite sure how describe what I am trying to do so I attached a document that is an exact copy of the original and entered some sample numbers and dates. The page has 5 trades per row, then it starts over. The idea is to check the date on each trade and add them up month by month, then present them. I would be interested in presenting them just to the right of the first 5 trades, and on another tab. I think once you look at the file it will make more sense. I left the description on the right side of the tab after the 5th trade. I hope it will make sense and someone has an idea how to go about it if I don't want to do it manually every time.

    Thank you for taking the time to look at it...

    Test (Montly Sales).xlsm

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I think this is what you are looking for.

    It creates a table from your data and then uses COUNTIF and SUMIF to get needed information

    Was not sure if you wanted number of trades each month or $ amount of profit each month so I put in both.

    Hope this helps.

    td
    Attached Files Attached Files

  3. The Following User Says Thank You to duthiet For This Useful Post:

    Maudibe (2015-06-05)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ferenc,

    An alternate method to Duthiet's clever approach is a User Defined Function (UDF). Place the code in a standard module then in cell AP5, enter this formula and copy down:

    =Mprofit(AQ5)

    The worksheet uses a named range called DateRange for all the cells with the date. The code will take the month in column AQ adjacent to the cell and assign a month number. Using the number it will look for the dates with that month and subtotal the profits for that month. In cell AP2 you use a sum formula with all the cell addresses in the formula. There is a second named range called Profits that contains all the Profit cells. The formula in AP2 =SUM(Profits)

    HTH,
    Maud

    Nagy.png

    In a standard module:
    Code:
    Public Function MProfit(mnth As String) As Currency
    Dim rng As Range
    Dim cell As Range
    Set rng = [DateRange]
    mon = Month([mnth] & " 1, 2015")
    MProfit = 0
    For Each cell In rng
        If Not IsDate(cell) Then Exit Function
        If Month(cell) = mon Then
            MProfit = MProfit + Cells(cell.Row + 22, cell.Column + 1)
        End If
    Next cell
    End Function
    Attached Files Attached Files

  5. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you both of you for the help.
    Maudibe your way is what I was looking for just because it is more elegant and does the job continuously regardless if I add more trades later or not, however I hit a wall and for the life of me can't figure out why I can't recreate it in a new file. I have uploaded a newly created file that has your code and yet gives an error message on all fronts. Could you please take a look and tell me what am I missing please?


    Book1.xlsm

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ferenc,

    You had to recreate the named ranges, DateRange and Profits. The attached book is you most recent Book1 that has been renamed and includes the added named ranges.

    Hats off to duthiet for creating a non vba method that often requires more forethought and ingenuity.


    HTH,
    Maud
    Attached Files Attached Files

  7. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I see it now. How would I go about using dynamic name range? I know the number of trades will increase but not sure by how much, so it would be very helpful if possible...

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ferenc,

    The following code will dynamically adjust as you add more trades assuming you continue to add trades in the order of 5 across then adding the next set below them (without skipping).

    In a standard module:
    Code:
    Public Function MProfit(mnth As String) As Currency
    Application.Volatile
    mon = Month([mnth] & " 1, 2015")
    MProfit = 0
    For I = 2 To 5000 Step 24
        For J = 7 To 39 Step 8
            If Not IsDate(Cells(I, J)) Then Exit Function
            If Month(Cells(I, J)) = mon Then
                MProfit = MProfit + Cells(I + 22, J + 1)
            End If
        Next J
    Next I
    End Function
    To dynamically add your profits in cell AP2, you could simply sum the newly calculated values in AP5 through AP16 using the formula =SUM(AP5:AP16) or you could use a user defined function to add them directly from the cells with the values. In AP2 enter the formula =SumProfits()

    In a standard module:
    Code:
    Public Function SumProfits() As Currency
    For I = 24 To 5000 Step 24
        For J = 8 To 40 Step 8
            If Not IsDate(Cells(I - 22, J - 1)) Then Exit Function
                SumProfits = SumProfits + Cells(I, J)
        Next J
    Next I
    End Function
    In your sample, how do you plan to differentiate adding the months by specific years. "January" in column AQ could be 2015 and/or 2016 but maybe it is not significant.

    HTH,
    Maud
    Attached Files Attached Files

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    Ferenc Nagy (2015-06-07)

  10. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    That is absolutely fantastic now! Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •