May 19 2021 08:05 AM
Hello,
I need to build a row reference based on two criteria.
1. Rows stating "New"
2. The #1, 2, 3, 4, 5 top invoices values, found in a second column.
a. Note: I want the formula to be specific to allow me to return the 1st largest "new", then the 2nd largest "new", then then 3rd largest "new", so on and so on.
I'm successful in returning the top 1-5 "New" values, based on the following formulas, but it returns a value, not a row reference.
=SUMPRODUCT(LARGE((Range1="New")*(Range2),{1}))
=SUMPRODUCT(LARGE((Range1="New")*(Range2),{2}))
=SUMPRODUCT(LARGE((Range1="New")*(Range2),{3}))
=SUMPRODUCT(LARGE((Range1="New")*(Range2),{4}))
=SUMPRODUCT(LARGE((Range1="New")*(Range2),{5}))
I'm attaching a sample data set, similar to what I'm working with. I need the formula to return rows 4, 6, 10, 13, 16. Obviously, this is simplified due to data sampling. But in the real world, there will be 100-120 "New" lines, and 5-5.5k total lines. "Open Amount" may not always be sorted from largest to smallest.
Thanks in advance for any help!!
May 19 2021 08:13 AM
SolutionSee the attached version.
Warning: you'll get incorrect results if there are ties among the top 5.
May 19 2021 08:36 AM
Thanks @Hans Vogelaar !!
The first reference returns correctly, however, the first row position repeats on the following 4.
For note: I am building a dashboard in a separate sheet. That sheet holds this formula. Will that impact the approach?
Thanks again.
Ryan
May 19 2021 08:43 AM
May 19 2021 08:51 AM
May 19 2021 08:13 AM
SolutionSee the attached version.
Warning: you'll get incorrect results if there are ties among the top 5.