Sumif on filtered data

Question

I have a large data set (15000 rows) consisting of Sales Office, Sales Rep, Customer, Product, Prior Year Sales, and Current Year Sales.  I need to see current and prior year sales by product, summed according to the data as it is filtered.  This file is used to have discussions with area managers and individual sales reps, and will be filtered in various combinations of sales office, sales rep, customer, and product.  For instance, I may want to see all sales to Wal-Mart, regardless of sales office. Or, I may need to see all Honey sales for sales office 102, or all of Clark Kent's sales regardless of customer or product.  

Is there a way to set up Sumif formulas below the data to sum sales by product, which updates the sums as the data is filtered?

A - Sales Office	B - Sales Rep	C - Customer	D - Product	E - Prior Year Sales	F - Current Year Sales
1 - 101	Bruce Wayne	Wal-Mart	Honey	1000	1500
2 - 102	Clark Kent	McDonald's	Honey	800	350
3 - 103	Peter Parker	PepsiCo	Vanilla	150	250
4 - 101	Steve Austin	Target	Salt	750	750
5 - 102	Clark Kent	Albertson's	Cocoa	2225	2250
6 - 102	Clark Kent	Wal-Mart	Pepper	800	700
7 - 103	Selina Kyle	PepsiCo	Garlic	100	300
8 - 102	Diana Hippolyta	McDonald's	Honey	500	450

Answer 1

Pivot tables have lots of options, so it is probably possible, but you'd have to experiment.

Answer 2

For simplicity's sake, I left out the calculations I'm doing with the data.  I have additional columns for year-over-year growth both in dollars and in percent.  Is there a good way to incorporate these into the pivot table and into the sums/subtotals?  Full example is below:

A - Sales Office	B - Sales Rep	C - Customer	D - Product	E - Last Year Sales	F - This Year Sales	G - Sales  Growth $	H - Sales Growth %

I'd like to be able to set up my data so that I can see the sales growth $ and % based on the filtered data.  Is this possible in a pivot table?

Answer 3

I'd create a pivot table based on the data. You can slice and dice the data any way you want in a pivot table.