Sum based on like items in two list

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

In one column, I have who(name).
The next, item sold.
The last I have the sales amount.

a) What formula can i use to extract items that are unique to Name +
Items sold and break them out in a column?

b) How can I sum totals for the extracted criteria?


a. a. b
Name Item Sold Sales Amount <break> Name Total
Items Sold Sales Amount
Larry Juice Cups $5
Larry Juice Cups $10
Larry Juice Cups $5
Larry Pickles $5
Amy Pickles $5
Amy Pickles $5
Amy Juice Cups $5
Amy Juice Cups $5
Larry Pickels $5
Derron Juice Cups $20
Derron Juice Cups $5
Derron Juice Cups $5
Derron Juice Cups $5
Derron Juice Cups $5



Thanx
 
Copy the two columns Name and Item Sold including the header into a
new worksheet. Highlight the data plus headers then click on Data |
Filter | Advanced Filter - in the pop up select Unique Records only
together with Copy to Another Location - specify D1 as the Location,
then click OK. You should have your unique list (with headers) in
columns D and E, and you can delete columns A to C if you want to.

Then in the new C2 on sheet2 you can enter this formula to give you a
count of the items sold by each person:

=SUMPRODUCT((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$100=B2))

and this formula in D2 will give you the sales amount:

=SUMPRODUCT((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$100=B2)*(Sheet1!C$2:C
$100))

I've assumed you have up to 100 rows of data in Sheet1 - adjust
references to suit your situation. Copy the formula down to cover the
number of rows you are using in columns A and B.

Hope this helps.

Pete
 
In one column, I have who(name).
The next, item sold.
The last I have the sales amount.

a) What formula can i use to extract items that are unique to Name +
Items sold and break them out in a column?

b) How can I sum totals for the extracted criteria?


a. a. b
Name Item Sold Sales Amount <break> Name Total
Items Sold Sales Amount
Larry Juice Cups $5
Larry Juice Cups $10
Larry Juice Cups $5
Larry Pickles $5
Amy Pickles $5
Amy Pickles $5
Amy Juice Cups $5
Amy Juice Cups $5
Larry Pickels $5
Derron Juice Cups $20
Derron Juice Cups $5
Derron Juice Cups $5
Derron Juice Cups $5
Derron Juice Cups $5



Thanx
Click to expand...

Select some cell in your table.

Data/Pivot Table (or in 2007-- Insert/Pivot table)

Drag Names to Rows
Items sold to Columns
Sales Amt to Data or Values
Choose or omit grand totals for rows or columns.

Pick one of the designs or formats.

Here's one with your data:

Sum of Sales Amount
Juice Pickles Totals
Amy 10 10 20
Derron 40 40
Larry 20 10 30
Totals 70 20 90

--ron
 
Back
Top