min/max by sales person and count of entries by sales person

[markhexcel]

Hi

I have some data, similar to below which is sales data for each sales peson in my firm. The info I have is the name, the quoted value and the sold value. I estimate by the end of the month I will have over 400-500 entries so dont want to do this manually.

I have been able to use the sumif function to tell me the totals that each sales person has sold and quoted.

Now I want to work out the

min and max that each sales person has quoted and sold

and

The number of sales each sales person has had

Are there formulas to do this?

Ive looked at the min/max functions but I believe they will only give me the min and max for all of the quoted and sold whereas I want this by sales person.

I have also looked at the count function but this will only provide a count of the entire number of sales and quoted, again I want this by sales person.

Sales Person	Quoted	Sold
Tom	£3000
Barry	£200	£200
Jane	£1200	£1200
Jim	£110
Barry	£23	£23
Bob	£2954
Jim	£40	£40
Jane	£2899	£2899
Tom	£1834	£1834
Jane	£12	£12
Dave	£899	£899

<tbody>
</tbody>

 

[StefanVL]

Hello there,

For the number of sales you should try and use the countif formula which needs the following (using the table you are using in your example):

=COUNTIF(A2:12;"TOM") or you could use a cellreference by using A2 instead of "Tom" giving you : =COUNTIF(A2:12;A2) giving you 2 as a result.

For the min/max per employee, are these values always equal as stated in your example or could they also differ?

 

[PaddyD]

Pivot tables are the answer