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$10 0=B2))

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

=SUMPRODUCT((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$10 0=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

On May 2, 3:57*pm, "J.W. Aldridge" <jeremy.w.aldri...@gmail.com>
wrote:
> 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

Look at Pivot Tables, they are designed to do this sort of thing.


On Fri, 2 May 2008 07:57:42 -0700 (PDT), "J.W. Aldridge"
<jeremy.w.aldridge@gmail.com> wrote:

>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
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

On Fri, 2 May 2008 07:57:42 -0700 (PDT), "J.W. Aldridge"
<jeremy.w.aldridge@gmail.com> wrote:

>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

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