Forum Discussion
Row Reference - Multiple Variables
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!!
See the attached version.
Warning: you'll get incorrect results if there are ties among the top 5.
5 Replies
See the attached version.
Warning: you'll get incorrect results if there are ties among the top 5.
- Also, I noticed that when I CRTL+Shift+Enter on your sample, all values flash and refresh. When I do it on mine, only the cell that's being edited flashes.
Thanks HansVogelaar !!
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
- Answering my own questions here. I had to expand the ROW() piece to include all the in-scope rows, and then grab all 5 formula cells and CTRL+Shift+Enter at once... This did it.
Also, I placed it into an INDEX() formula and works beautifully.
Thanks again!!
