Excel Cumulative Sum By Sales Rep

[Ang Ang]

Hi Excel Expert,

I can't seem to find any formula to calculate cumulative Sales by sales rep(without sorting by sales rep/date). Please help. Attached is the file if it will help.

For example:

I am adding the sales for the sales rep each day so I would like to get the cumulative sales. For Bob, the first sales = 400, then next sales = 500 the total would be 900. Then on 7/3 another sale = 500, now the total sales would be 400+500+500 = 1400.


Date Sales Rep Sales Cumulative Sales By Sales Rep Commission Rate
1-Jul Bob 400 400 3%
1-Jul Bob 500 900 8%
1-Jul Sal 1000 1000
2-Jul Tom 1000 1000
3-Jul Sally 1000 1000 12%
3-Jul Bob 500 1400 10%
4-Jul Sally 1000 200 12%


Thank You

 

[r2c2]

@Ang Ang

You can use mixed reference style to get such cumulative totals, like this:

=SUMIFS($H$2:H2,$G$2:G2,G2)

 

[David Evans]

Here's a slightly different way of calculating a tiered commission schedule - I've created a User Defined Function (UDF) that is essentially a piece of VBA code that references the Sales Commission Table ("Sales_Comm") and a Pivot Table based on your original Sales Data (which I made into an Excel Table - "Sales_Data")

In the example the Commission Due is not part of the Pivot Table, although it is adjacent to it.

Try adding/editing values to the Data Sales Table. refresh the Pivot and see what happens.

This can be improved upon - I've tried using the UDF in a Calculated Field, but couldn't get it to work, but it's late and I'm tired.

 

[Ang Ang]

@Ang Ang

You can use mixed reference style to get such cumulative totals, like this:

=SUMIFS($H$2:H2,$G$2:G2,G2)

 

[Ang Ang]

Thank you!