# Dynamic range of rows for SUM function

#### Pratap

##### New Member
Hi All

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.

#### Attachments

• 46.8 KB Views: 9

##### 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,

#### 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,

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

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.

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.

##### Active Member
Very neat formula!!

#### Pratap

##### New Member
Thank you Mike.is there any way to have a vba code for it?

#### 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

• 21.5 KB Views: 2
Last edited:

@Mike

@Mike