SOLVED

Row Reference - Multiple Variables

%3CLINGO-SUB%20id%3D%22lingo-sub-2368463%22%20slang%3D%22en-US%22%3ERow%20Reference%20-%20Multiple%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368463%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20build%20a%20row%20reference%20based%20on%20two%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Rows%20stating%20%22New%22%3C%2FP%3E%3CP%3E2.%20The%20%231%2C%202%2C%203%2C%204%2C%205%20top%20invoices%20values%2C%20found%20in%20a%20second%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20a.%20Note%3A%20I%20want%20the%20formula%20to%20be%20specific%20to%20allow%20me%20to%20return%20the%201st%20largest%20%22new%22%2C%20then%20the%202nd%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20largest%20%22new%22%2C%20then%20then%203rd%20largest%20%22new%22%2C%20so%20on%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20successful%20in%20returning%20the%20top%201-5%20%22New%22%20values%2C%20based%20on%20the%20following%20formulas%2C%20but%20it%20returns%20a%20value%2C%20not%20a%20row%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT(LARGE((Range1%3D%22New%22)*(Range2)%2C%7B1%7D))%0A%3DSUMPRODUCT(LARGE((Range1%3D%22New%22)*(Range2)%2C%7B2%7D))%0A%3DSUMPRODUCT(LARGE((Range1%3D%22New%22)*(Range2)%2C%7B3%7D))%0A%3DSUMPRODUCT(LARGE((Range1%3D%22New%22)*(Range2)%2C%7B4%7D))%0A%3DSUMPRODUCT(LARGE((Range1%3D%22New%22)*(Range2)%2C%7B5%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attaching%20a%20sample%20data%20set%2C%20similar%20to%20what%20I'm%20working%20with.%26nbsp%3B%20I%20need%20the%20formula%20to%20return%20rows%204%2C%206%2C%2010%2C%2013%2C%2016.%26nbsp%3B%20Obviously%2C%20this%20is%20simplified%20due%20to%20data%20sampling.%26nbsp%3B%20But%20in%20the%20real%20world%2C%20there%20will%20be%20100-120%20%22New%22%20lines%2C%20and%205-5.5k%20total%20lines.%26nbsp%3B%20%22Open%20Amount%22%20may%20not%20always%20be%20sorted%20from%20largest%20to%20smallest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2368463%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368493%22%20slang%3D%22en-US%22%3ERe%3A%20Row%20Reference%20-%20Multiple%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1057995%22%20target%3D%22_blank%22%3E%40LandscapeDataNut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3EWarning%3A%20you'll%20get%20incorrect%20results%20if%20there%20are%20ties%20among%20the%20top%205.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368572%22%20slang%3D%22en-US%22%3ERe%3A%20Row%20Reference%20-%20Multiple%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368572%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20reference%20returns%20correctly%2C%20however%2C%20the%20first%20row%20position%20repeats%20on%20the%20following%204.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20note%3A%20I%20am%20building%20a%20dashboard%20in%20a%20separate%20sheet.%26nbsp%3B%20That%20sheet%20holds%20this%20formula.%26nbsp%3B%20Will%20that%20impact%20the%20approach%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERyan%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies
best response confirmed by LandscapeDataNut (New Contributor)
Solution

@LandscapeDataNut 

See the attached version.

Warning: you'll get incorrect results if there are ties among the top 5.

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

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

@LandscapeDataNut 

Good to hear that!