SUMIFS between dates where dataset has multiple entries

[rexhvn]

Hi all,

I'm hoping for some help and guidance as to how I can achieve what I'm trying to do.

Essentially, what I am trying to do is show the top 5 results based on value ($$) between two dates (=<&CELL & =>&CELL) where the lookup value has multiple lines.

I'll try explain in more detail. I have a list of 10k+ lines. In these lines, I have an account number (multiple lines with the same account number), Customer Name, Dates & values. I want to some how create a forumla(s) that will help me understand out of these lines, which customer's have the highest total within the given date range and display the top 5.

End Result will be:
Customer, Customer Name, Value (Highest)
Customer, Customer Name, Value (2nd highest)
Customer, Customer Name, Value (3rd Highest)

The most difficult part would be understanding which customer (customer ID) has the highest value, and how to pull that in. The rest I could always do a VLOOKUP or SUMIFS.

What would be the best way to accomplish this?

Thank you

 

[t0ny84]

Assuming you will be displaying this information on another sheet the following should assist if I have understood your question correctly.

The below formula is based on:
- A New Sheet has been created in the Excel Workbook.
- A list of unique Customer Names has been added to a new sheet in column A.
*You can either copy and paste the Customer Name column and then delete duplicates or use the UNIQUE function within EXCEL to get a list of UNIQUE Customer Names.

-This list of Customer Names starts from A2 (Header Row)
- B1 and C1 are used for the Start and End Dates.

SUMIFS(
TABLE_NAME[VALUE COL],
TABLE_NAME[CUST_NAME], A3, 
TABLE_NAME[DATE], ">="&B1
TABLE_NAME[DATE], "<="&C1)

This formula should get you the total based on the customer's name within the dates entered in B1 and C1.

You could then sort this table from largest to smallest to see the top 5.

If you can supply an example of the spreadsheet I can try to help a bit more.

t0ny84

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)