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

markhexcel

New Member
Joined
Jan 7, 2014
Messages
1
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 PersonQuotedSold
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>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146
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?
 

Forum statistics

Threads
1,137,060
Messages
5,679,372
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top