Any Excel Gurus out there?

[Moff Rimmer]

I'm looking for a way to do a double conditional sum and/or average from a list. (Or count?)

Think of it this way -- If I have a list of car salesmen, the type of car, and the commission made on each car. I'd like a fast way to give me a total and average of the Ford cars that Bob sold, the Chevy cars that Bob sold, etc.

There are a number of these I'd like to gather from the same list and would prefer not to separate them out because I'm looking for group totals and averages as well.

Thanks for any help out there.

[CourtFool]

Have you looked at DAVERAGE and DSUM?

[Moff Rimmer]

CourtFool wrote:

Have you looked at DAVERAGE and DSUM?

I did but the "help" wasn't very helpful. I was going to experiment myself -- any pointers on how to make it work right? (It talked about a "table" and I don't know if I technically have a "table".)

[CourtFool]

Let us assume in column A you have the type of cars (Ford, Chevy). In column B you have salespeople (Bob, Joe). In column C, you have commission.

Now, create another table with Column A for type of cars and column B salespeople.

In your function, the first argument will be your data (first table) of cars, people and commissions. The second argument is the name of the column you want to perform the calculation (sum or average); the heading of your commissions. The final argument will be the range of values from your second table that contain the values you want to be true in your first table. So you will select a range to include Bob and Ford.

Does that help at all? It is kind of confusing.

[CourtFool]

Assuming the example provided looks like mine, swap Cars for Tree and Height for Salesperson.

[sozin]

Use pivot tables. If your raw data set looks like:

Date of Sale, Salesman, Car Type, Sale Amount, Commission Amount
1/1/2010, Bob, Ford, 10000,1000
1/2/2010,Joe,Chevy,5000,500
1/3/2010,Bob,Ford,12000,1200

etc, then select the dataset, insert a pivot table, then drag Salesman column as a row label, the car type as as the column label, the Sale Amount in as a value (expressed as a sum), and the Sale amount in as a value (expressed as an average).

EDIT: Because I'm a nice guy I created an example for you. Uploaded an Excel 2003 format spreadsheet to my shared google docs site. This is the link.

[CourtFool]

sozin wrote:

Use pivot tables.

I had not thought of that. Excellent suggestion.

[Tensor]

Moff Rimmer wrote:

CourtFool wrote:

Have you looked at DAVERAGE and DSUM?

I did but the "help" wasn't very helpful. I was going to experiment myself -- any pointers on how to make it work right? (It talked about a "table" and I don't know if I technically have a "table".)

Seriously. Did you try Google?

[Moff Rimmer]

sozin wrote:

Use pivot tables.

Thanks all. It had been a while since I used Pivot Tables, but that was probably the easiest and/or best way to get the information I needed.

Thanks again for the help.