Sorting monthly sales after checking date…

Topic

New Reply

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…

40897-Test-Montly-Sales

Reply | Quote

Replies

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

Reply | Quote

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

40919-Nagy

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

Reply | Quote

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?

40921-Book1

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

That is absolutely fantastic now! Thank you very much! 🙂

Reply | Quote