Help with YTD Sales Summary

**RubiksCuber** · 01-27-2013, 04:43 PM

Ok so here's the skinny. I have a large number of sales agents that I am required to manually track sales for over the next few months. As the sales get made, by week - i am throwing them in this spreadsheet. I want the spreadsheet to rank them by units sold largest to smallest - by week - and I also want to see the YTD performance ranked the same way. I have played with the pivot chart wizard and i just can't get it to sort it the way I want and the online tutorials aren't really helping me get there...anyone have any suggestions?

**FDibbins** · 01-27-2013, 04:57 PM

Hi

Not sure where you want to put this, I added a column after REVENUE...

=IF(C3=0,"",RANK(C3,$C$3:$C$600)) copied down. Change the column C reference to B if you want to rank based on volume rather than value

As far as the YTD summary is converned, I would offer this advice. Intsead of having eash week in it's own workseet, put ALL the data into 1 sheet, andthen base all your calc off of that. The only extra item you would need to add would be the date for each record

If you feel you still want each weelk the way you have it, you can create a summary sheet by following these instructions...

1. add a blank worksheet at the start and end of your data sheets, and call them Start and End,
2. Create a summary sheet that looks identical to the other sheets.
3. when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are.

this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though