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
My Excel report (how it should look)
This is what I have so far for 1st largest value:
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!
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))
Thank you!