• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic range of rows for SUM function

P

Pratap

New Member
Hi All

Can you please help for the
required solution:

need to have the SUM value Based on the Day and Sales person.

1.Salesman A selles some of the 20 varies of the fruits(for example)
2.Daily Sales of Salesmans will be downloaded from database.so it is not sure until we downloaded howmany types of fruits the salesman sold.
it could be 2 or 25 or 18.
3.Some day Salesman A sells 5 varieties of Fuits and other day 8 varities. it is not fixed
4.hence number of Rows for salesman A is bnot fixed.it will get change every day
5.I need a formula that can dynamically adjust every day for the salesman A range and sum the total value of the sales for the day.
6.I have nearly 2500 salesman and every salesman sales are like A.
7.need to total every salesman everyday with dynamically adjusted range.

please help.
 

Attachments

  • DRange.JPG
    DRange.JPG
    46.8 KB · Views: 9
Hi Pratap,

You can achieve this with Pivot Table with more efficiently. Or you need to sort the data by Salesman (if not sorted), and use SubTotal inbuilt function with sum when date changes.

Regards,
Prasad DN
 
prasaddn said:
Hi Pratap,

You can achieve this with Pivot Table with more efficiently. Or you need to sort the data by Salesman (if not sorted), and use SubTotal inbuilt function with sum when date changes.

Regards,
Prasad DN
Click to expand...


Thank you Prasad
With Pivot table,I need to select the parameters for each salesman and it is not practicable for 2500 sales people. I even tried vlookup, but failed as it not working on text/strings.
 
Pratap said:
Hi All

Can you please help for the
required solution:

need to have the SUM value Based on the Day and Sales person.

1.Salesman A selles some of the 20 varies of the fruits(for example)
2.Daily Sales of Salesmans will be downloaded from database.so it is not sure until we downloaded howmany types of fruits the salesman sold.
it could be 2 or 25 or 18.
3.Some day Salesman A sells 5 varieties of Fuits and other day 8 varities. it is not fixed
4.hence number of Rows for salesman A is bnot fixed.it will get change every day
5.I need a formula that can dynamically adjust every day for the salesman A range and sum the total value of the sales for the day.
6.I have nearly 2500 salesman and every salesman sales are like A.
7.need to total every salesman everyday with dynamically adjusted range.

please help.
Click to expand...
Hi,

If you want a formula put this in E2 and drag down.


=IF(AND(A2=A3,C2=C3),"",SUM($D$2:D2)-SUM($E$1:E1))
 
Thank you Mike
The formula provided is great but it only works on fixed number of rows.when new week data downloaded,the formula you provided will not work automatically.it need to be copied again.thank you for this great help.
Pratap
 
Pratap said:
Thank you Mike
The formula provided is great but it only works on fixed number of rows.when new week data downloaded,the formula you provided will not work automatically.it need to be copied again.thank you for this great help.
Pratap
Click to expand...
Hi,

The formula is automatic and you can copy it down as far as you want. It will return blank cells when there are no data in columns A, B, C & D.
 
Pratap said:
Thank you Mike.is there any way to have a vba code for it?
Click to expand...
Hi,

Unless you're familiar with VB code you may struggle to adapt this. If you do then attach a workbook with some data in. See attached workbook.

Code: 
Sub Daily_sums()
Dim LastRow As Long, x As Long
Dim MyRange As Range, c As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A2:A" & LastRow)
Range("E2").Resize(LastRow).ClearContents
For Each c In MyRange
  If c.Value <> c.Offset(1).Value Or c.Offset(, 2).Value <> c.Offset(1, 2).Value Then
  c.Offset(, 4) = WorksheetFunction.Sum(Cells(2, 4).Resize(c.Row - 1)) _
  - WorksheetFunction.Sum(Cells(2, 5).Resize(c.Row - 1))
  End If
Next
End Sub
 

Attachments

  • Daily_Totals.xlsm
    21.5 KB · Views: 2
Last edited:
You must log in or register to reply here.
Back
Top