Help with Index/ Small formula to return valid values and not give a blank row for cells that don't

[Bukemm]

Hi,
I've been googling and reading threads on here describing how to use Index/Small functions to return a cells values if it meets a given criteria without giving lots of blank rows where the criteria doesn't match, as it would with an IF formula

I haven't been able to adapt these to fit my case, so hoping for some advice

Essentially I have a report that has 60k+ lines showing product revenues and costs among other data. I want to add a summary tab that shows the company, material, revenue and cost for any part that has revenue above $50k, and costs higher than revenue (I'll use a cell reference for the $50k figure so it can be adjusted). I can do this with nested if's, but I'll end up with 60000 rows of blank cells for 20 valid lines of data, and I want it to be dynamic so the summary tab works if the user changes the criteria used for the main body of data (for example, refreshing it for a different year / different set of companies)

I've attached an image of some example data. Other threads indicate this can be done with something like:

{=IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=TRUE,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($1:1))),"")}

As a first step I wanted to see if I could get it to pull through all the lines with revenue above 50k, so I've tried
=IFERROR(INDEX('BW Data'!$A$1:$J$100000,SMALL(IF($C$1:$C$100000>50000,ROW($C$1:$C$100000)-ROW($C$1)+1,Rows($1:1)),),"")

but that doesn't work (I get an error about too few arguments), and i don't understand it well enough to begin to troubleshoot. Ideally I want to replace the IF>50,000 with an IF(AND) to say if revenue Greater than 50,000 and -Cost > Revenue, display the company in Cell A5 on the summary sheet, material in B5, revenue in C5, cost in D5, and drag it down so the next valid result populates in A:D6 etc.

Any advice greatly appreciated!

 

[Fluff]

For that first criteria try

=IFERROR(INDEX('BW Data'!$A$1:$A$100000,AGGREGATE(15,6,(ROW('BW Data'!$C$1:$C$100000)-ROW('BW Data'!$C$1)+1)/($C$1:$C$100000>50000),ROWS(A$2:A2))),"")

Assumes the formula is going in A2 if not change the A$2:A2 to suit.

 

[Bukemm]

For that first criteria try

=IFERROR(INDEX('BW Data'!$A$1:$A$100000,AGGREGATE(15,6,(ROW('BW Data'!$C$1:$C$100000)-ROW('BW Data'!$C$1)+1)/($C$1:$C$100000>50000),ROWS(A$2:A2))),"")

Assumes the formula is going in A2 if not change the A$2:A2 to suit.

Thank you so much for the quick reply. That works

May I ask your advice on how to improve the $C$1:$C$100000>50000 logic so that it would only show if the revenue was above $50,000, and the revenue - cost was also less than 0?

 

[Bukemm]

Sorry for the double-post, couldn't see an option to edit.

I think I've answered my own question - I should be able to use this logic onto a 'Working' tab, add a formula to do the revenue - cost section, and then make a third summary tab that pulls through from that using the same logic but checking that new field for values <0

Busy mocking it up now, but thanks again for your support!

 

[Fluff]

Glad to help & thanks for the feedback.

For 60k rows, a helper column to do the revenue - cost, is probably the best way to do it.