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

[brncao]

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:

=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!

 

[Anthony47]

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:

=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:

=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]

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]

Just installed the Add-In. Hope this helps.

Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	Date	Amount	Text
2	1/10/2020	$ 15,000,000.00	ABC	2nd Largest in Jan (ABC)
3	1/15/2020	$ 600,000.00	ABC
4	1/25/2020	$ 30,000,000.00	ABC	1st Largest in Jan (ABC)
5	1/28/2020	$ 250,000.00	Misc
6	2/10/2020	$ 20,000,000.00	ABC	2nd Largest in Feb (ABC)
7	2/15/2020	$ 40,000,000.00	Misc
8	2/25/2020	$ 35,000,000.00	ABC	1st Largest in Feb (ABC)
9
10	Wrong results	1/1/2020	1/2/2020	1/3/2020	1/4/2020	1/5/2020	1/6/2020	1/7/2020	1/8/2020	1/9/2020	1/10/2020	1/11/2020	1/12/2020	1/13/2020	1/14/2020	1/15/2020	1/16/2020	1/17/2020	1/18/2020	1/19/2020	1/20/2020	1/21/2020	1/22/2020	1/23/2020	1/24/2020	1/25/2020	1/26/2020	1/27/2020	1/28/2020	1/29/2020	1/30/2020	1/31/2020	2/1/2020	2/2/2020	2/3/2020	2/4/2020	2/5/2020	2/6/2020	2/7/2020	2/8/2020	2/9/2020	2/10/2020	2/11/2020	2/12/2020	2/13/2020	2/14/2020	2/15/2020	2/16/2020	2/17/2020	2/18/2020	2/19/2020	2/20/2020	2/21/2020	2/22/2020	2/23/2020	2/24/2020	2/25/2020	2/26/2020	2/27/2020	2/28/2020	2/29/2020	3/1/2020
11	Item 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	$ -	$ -	$ -	$ -	$ -
12	Item 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
14	What it should show
15	Item 1	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 30,000,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 35,000,000.00	$ -	$ -	$ -	$ -	$ -
16	Item 2	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 15,000,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 20,000,000.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Sheet1

Cell Formulas
Range	Formula
B11:BJ11	B11	=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:BJ12	B12	=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]

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

 

[jasonb75]

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]

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]

This appears to work, although I'm not sure how efficient it would be with larger data sets.

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