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

brncao

Board Regular
Joined
Apr 28, 2015
Messages
139
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
DateAmountText
1/10/2020​
$ 15,000,000.00ABC
1/15/2020​
$ 600,000.00ABC
1/25/2020​
$ 30,000,000.00ABC
1/28/2020​
$ 250,000.00Misc
2/10/2020​
$ 20,000,000.00ABC
2/15/2020​
$ 1,000,000.00Misc
2/25/2020​
$ 35,000,000.00ABC

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
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
Joined
Dec 30, 2008
Messages
12,667
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2015
Messages
139
Just installed the Add-In. Hope this helps.

Formula.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
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
Joined
Mar 29, 2006
Messages
2,147

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,667
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2015
Messages
139
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
Joined
Dec 30, 2008
Messages
12,667
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,288
Members
417,135
Latest member
zeusmining

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