Find highest number of sales in a date range and show seller and sale number

Hi! (hit return before getting all tags in and don't know how to edit them back in...sorry about this)

I have a couple worksheets I use to keep track of sales. I've attached an example, but to explain what I am looking for:

Worksheet1 has a daily list of sales that looks something like this:

Row 1 is a header for all columns:

Column C is date...column D is time....Column E is seller's name

*each row represents 1 sale*


I'd like to find out who sold the most in a given month...and report that info on Worksheet2:

Wksht2 Row 1 is for headers. Row 2 and down is for the data:

Column A is Month...column B is other info.....column C is for seller with most sales....column D is how many sales the seller had that month


Workbook1 example.xlsx

I appreciate your help and look forward to your ideas! I've gotten some GREAT help here since discovering the site and am very thankful for your time!

Hi audiofreak,

Try using below array formula :-

{=SUM(--((Sheet1!$C$2:$C$7>=DATE(YEAR(Sheet2!$A2),MONTH(Sheet2!$A2),1)*(Sheet1!$C$2:$C$7>=EOMONTH(Sheet2!$A2,0))))*((Sheet1!$E$2:$E$7=$C2)))}

Enter this using key combination Ctrl Shift Enter. Thanks.

Workbook1 example.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi audiofreak,

Please check the attached workbook in my post#2. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

I did, actually, and I see where you put the formula and I did the same, then dragged down. I wonder why it's not working for me. I changed the sheet names to my sheet names, then adjusted the range to my desired range. but it doesn't seem to work. I see it work in the sample you did for me and can't figure out why it's not working for my actual worksheets. Hmmmm... could I have messed something up in changing my sheet names or the ranges? I have close to 400 rows now, and it will continue to grow as sellers make daily sales.

Here is the exact formula I used on sheet2, called "monthly", for row 2. I put the formula on D2 (Sheet 1 is called "Daily"): {=SUM(--((Daily!$C$2:$C$500>=DATE(YEAR(Monthly!$A2),MONTH(Monthly!$A2),1)*(Daily!$C$2:$C$500>=EOMONTH(Monthly!$A2,0))))*((Daily!$E$2:$E$500=$C2)))}

I notice that if I change the date in the sample worksheet1 it doesn't register the change in sample worksheet 2 and change the result.

I am sure it's something simple that I am not grasping, and appreciate your patience.

Not sure.. but do you have your excel calculations set to "Automatic"..
Try uploading the workbook with the formula you applied.. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>