Want to return the highest total from a table based on different criteria

[JBNTS]

Hi there,

I am trying to find a formula that will allow me to return the Salesperson that has sold the most Units in 1 month.

I know ways to do it by creating a second table to sum the amounts, but I want one cell that simply shows the Salesperson's name.

The data is set out by DATE, UNITS SOLD, REVENUE, SALES PERSON.

The date is entered as MM/DD/YYYY.


Screen Shot 2016-05-16 at 3.31.05 PM.png

[jeffreybrown]

Hello and Welcome to the Forum,

It would be helpful if we could actually see some data and an expected result!

One thing we do not know, how is the month entered? The name, an actual date, etc.

Are the salesperson names duplicated in column D even within the same month?

[JBNTS]

Hi Jeff,

I have attached a screenshot of the data which I hope clears things up a bit.

[Tony Valko]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[jeffreybrown]

I of course Agree with Tony 100%, but also, when you post an image, that would require members having to retype your information.

Anyway, how about just constructing a pivot table

Row Labels >> Date
Column Labels >> Sales Person
Values >> Max of Units Sold

Now group the dates in your pivot table by month.

http://www.techrepublic.com/article/...able-by-dates/

Now you have the ability to see the Max and who it is, but you can also see what the other salespeople have done.