Thread: Sorting monthly sales after checking date...

1. 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. 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

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

Maudibe (2015-06-05)

4. 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```

5. 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. 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

7. 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. Ferenc,

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

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

Ferenc Nagy (2015-06-07)

10. 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
•