• 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

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

prasaddn

Active Member
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
 

Pratap

New Member
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

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.
 

Mike H..

Active 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.
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))
 

Pratap

New Member
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
 

Mike H..

Active Member
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
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.
 

Mike H..

Active Member
Thank you Mike.is there any way to have a vba code for it?
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

Last edited:
Top