# Get 1st and 2nd Large Value in a Date Range with Criteria

#### brncao

##### Board Regular
Can someone help me with this problem? I am trying to construct a formula that will get the first and second largest value in a table between two dates (i.e. 1/1/2020-1/31/2020). Criteria is Text="ABC", LARGE function's scope should be on a monthly basis, and the report should populate where the date matches.

Database
 Date Amount Text 1/10/2020​ \$ 15,000,000.00 ABC 1/15/2020​ \$ 600,000.00 ABC 1/25/2020​ \$ 30,000,000.00 ABC 1/28/2020​ \$ 250,000.00 Misc 2/10/2020​ \$ 20,000,000.00 ABC 2/15/2020​ \$ 1,000,000.00 Misc 2/25/2020​ \$ 35,000,000.00 ABC

My Excel report (how it should look)
 ...​ 1/10/2020​ ...​ 1/25/2020​ ...​ Item 1 (1st Largest) \$ - \$ - \$ - \$ 30,000,000.00 \$ - Item 2 (2nd Largest) \$ 15,000,000.00 \$ -

This is what I have so far for 1st largest value:
Excel Formula:
``=SUMPRODUCT(LARGE((Table1[[Text]:[Text]]="ABC")*(Table1[[Date]:[Date]]>=DATEVALUE(MONTH(B\$10)&"-"&YEAR(B\$10)))*(Table1[[Date]:[Date]]<=EOMONTH(B\$10,0))*(Table1[[Amount]:[Amount]]),1)*(Table1[[Date]:[Date]]=B\$10))``
The problem is it's populating \$30M on 1/10/2020, 1/15/2020, 1/25/2020 (correct), and 1/28/2020. Those dates that aren't 1/25/2020 should display \$0. Your help would be very appreciated.

Thank you!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Anthony47

##### Well-known Member
From what you wrote I didn't understand wether you need to get the largest and the second largest in a month that you specify (ie: from 1st to 31st of October) OR in a period that you specify as beginning day and end day.
Now supposing that you want getting the largest from a specified beginning date to the end of the same month; and the beginning date is in B10 of current sheet; then for the largest you should use:
Code:
``=SUMPRODUCT(LARGE((Table1[[Text]:[Text]]=""ABC"")*(Table1[[Date]:[Date]]>=B10)*(Table1[[Date]:[Date]]<=EOMONTH(B10,0))*(Table1[[Amount]:[Amount]]),1))``

For the "second largest" just replace that "1" at the end of the formula by 2

If you wish getting the largest between a specified starting date and a specified ending date; and the starting date is in B10 and the eneding date is in C10; then for the largest you should use:
Code:
``=SUMPRODUCT(LARGE((Table1[[Text]:[Text]]=""ABC"")*(Table1[[Date]:[Date]]>=B10)*(Table1[[Date]:[Date]]<=C10)*(Table1[[Amount]:[Amount]]),1))``

Again, for the "second largest" just replace that "1" at the end of the formula by 2

If you meant something different then you should clarify even more what you need, and maybe share a testing workbook with the current data structure

Bye

#### jasonb75

##### Well-known Member
Those dates that aren't 1/25/2020 should display \$0.
Why? The other dates that you mention all fall within the specified range of 1/1/20 - 1/31/20 so that would mean that the results are correct.

#### brncao

##### Board Regular
Just installed the Add-In. Hope this helps.

Formula.xlsx
1DateAmountText
21/10/2020\$ 15,000,000.00ABC2nd Largest in Jan (ABC)
31/15/2020\$ 600,000.00ABC
41/25/2020\$ 30,000,000.00ABC1st Largest in Jan (ABC)
51/28/2020\$ 250,000.00Misc
62/10/2020\$ 20,000,000.00ABC2nd Largest in Feb (ABC)
72/15/2020\$ 40,000,000.00Misc
82/25/2020\$ 35,000,000.00ABC1st Largest in Feb (ABC)
9
10Wrong results1/1/20201/2/20201/3/20201/4/20201/5/20201/6/20201/7/20201/8/20201/9/20201/10/20201/11/20201/12/20201/13/20201/14/20201/15/20201/16/20201/17/20201/18/20201/19/20201/20/20201/21/20201/22/20201/23/20201/24/20201/25/20201/26/20201/27/20201/28/20201/29/20201/30/20201/31/20202/1/20202/2/20202/3/20202/4/20202/5/20202/6/20202/7/20202/8/20202/9/20202/10/20202/11/20202/12/20202/13/20202/14/20202/15/20202/16/20202/17/20202/18/20202/19/20202/20/20202/21/20202/22/20202/23/20202/24/20202/25/20202/26/20202/27/20202/28/20202/29/20203/1/2020
11Item 1\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 30,000,000.00\$ -\$ -\$ -\$ -\$ 30,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 30,000,000.00\$ -\$ -\$ 30,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 35,000,000.00\$ -\$ -\$ -\$ -\$ 35,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 35,000,000.00\$ -\$ -\$ -\$ -\$ -
12Item 2\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 15,000,000.00\$ -\$ -\$ -\$ -\$ 15,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 15,000,000.00\$ -\$ -\$ 15,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 20,000,000.00\$ -\$ -\$ -\$ -\$ 20,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 20,000,000.00\$ -\$ -\$ -\$ -\$ -
13
14What it should show
15Item 1\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 30,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 35,000,000.00\$ -\$ -\$ -\$ -\$ -
16Item 2\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 15,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ 20,000,000.00\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -\$ -
Sheet1
Cell Formulas
RangeFormula
B11:BJ11B11=SUMPRODUCT(LARGE((Table1[[Text]:[Text]]="ABC")*(Table1[[Date]:[Date]]>=DATEVALUE(MONTH(B\$10)&"-"&YEAR(B\$10)))*(Table1[[Date]:[Date]]<=EOMONTH(B\$10,0))*(Table1[[Amount]:[Amount]]),1)*(Table1[[Date]:[Date]]=B\$10))
B12:BJ12B12=SUMPRODUCT(LARGE((Table1[[Text]:[Text]]="ABC")*(Table1[[Date]:[Date]]>=DATEVALUE(MONTH(B\$10)&"-"&YEAR(B\$10)))*(Table1[[Date]:[Date]]<=EOMONTH(B\$10,0))*(Table1[[Amount]:[Amount]]),2)*(Table1[[Date]:[Date]]=B\$10))

#### Anthony47

##### Well-known Member

May I suggest that you take some time to respond to the questions that some of us did pose?

#### jasonb75

##### Well-known Member
I can see what you need from the larger example, the formula is going to be quite complex. Thinking out of the box before attempting it, what if the largest and 2nd largest values are equal (same value, same month, different day)? How should the results be shown then?
Similarly if 2nd and 3rd largest are equal should both be shown? If so, how would you want that fitted into your layout?

#### brncao

##### Board Regular
May I suggest that you take some time to respond to the questions that some of us did pose?
You asked to clarify by sharing a testing workbook. So I posted the entire spreadsheet when I learned about the add-in. Sorry if my first post was confusing. Hope it clarifies.

I can see what you need from the larger example, the formula is going to be quite complex. Thinking out of the box before attempting it, what if the largest and 2nd largest values are equal (same value, same month, different day)? How should the results be shown then?
I've thought of this but it is extremely unlikely that they have the same values, or where Item 1 and 2 is a false positive (i.e. misclassification where Item 1 has a smaller value than item 2). I will simply hard-code it if that ever happens.

Similarly if 2nd and 3rd largest are equal should both be shown? If so, how would you want that fitted into your layout?
It's only 1st and 2nd largest. There's no 3rd.

Here's a bit of background:
I'm pulling this data from Access where I have all our banking data imported. When a vendor sends us payment, it will show the vendor name ("ABC") on the bank statement, but no description as to what it is. I can only deduce that the largest amount in a month is always Item 1 and the 2nd largest is Item 2. We're talking 10s of millions apart between 1, 2, and 3; there's very little chance a 3rd largest value will come close to 2nd for it to be a false positive. So far that's correct 100% of the time and the chances for false positives is extremely low. Of course, if said vendor made late payment where it crosses over into the next month, I'll make note of it and make changes. I'll still reconcile the report anyways.

#### jasonb75

##### Well-known Member
This appears to work, although I'm not sure how efficient it would be with larger data sets.
Excel Formula:
``=SUMIFS(Table1[[Amount]:[Amount]],Table1[[Amount]:[Amount]],AGGREGATE(14,6,Table1[[Amount]:[Amount]]/(Table1[[Text]:[Text]]="ABC")/(Table1[[Date]:[Date]]>EOMONTH(B\$10,-1))/(Table1[[Date]:[Date]]<=EOMONTH(B\$10,0)),1),Table1[[Date]:[Date]],B\$10,Table1[[Text]:[Text]],"ABC")``
This would be better done with MAXIFS instead of SUMIFS if you're using a version of excel that supports the function, currently 2019 or 365. The syntax for the formula is exactly the same. With SUMIFS duplicate values (on the rare occasion that they might occur) would be summed, with MAXIFS they would not.

Last edited:

Replies
1
Views
53
Replies
2
Views
84
Replies
5
Views
106
Replies
2
Views
228
Replies
1
Views
91

1,130,251
Messages
5,641,101
Members
417,194
Latest member
Excellent Excel

### 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.

### Which adblocker are you using?

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

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