List cell entries based on numerical criteria

[jcswaby]

Hi I've been racking my brains on this for a couple of hours. I want to produce a summary list on sheet 1 of all account numbers (Sheet 2, column A) which have a value below 5000 (sheet 2, column B). I have searched using various terms but they all keep pointing out COUNTIF functions, I know the number of accounts, I just don't know which accounts.

Your help is greatly appreciated.

[FDibbins]

Try something like this...
=COUNTIF(Sheet2!B:B," < 5000")

[oeldere]

pivot table can help you solve your problem.

you get better help on your question if you add a small excel file without confidential information.

please also add the expected result manualy in your file.

[jcswaby]

ok I've attached an example of what I am looking for in the original question. Many thanks.

[FDibbins]

Your sample does not really compare to the initial question, and also, none of your sample values exceed the criteria, but try this...
=SUMPRODUCT(--(--MID(A2:A6,4,5) < 5000))

[oeldere]

with a pivot table.

[Tony Valko]

This array formula** entered in A2:

=IFERROR(INDEX(Sheet2!A:A,SMALL(IF(Sheet2!B$2:B$14 < 5000,ROW(Sheet2!B$2:B$14)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[jcswaby]

I'm not sure what you mean, in my question I just want a list of the actual accounts that are below 5000 (not the number of accounts). I have made five accounts which fit that criteria so those are the accounts I am trying to list on the first sheet.

[FDibbins]

Your sample does not really compare to the initial question, and also, none of your sample values exceed the criteria, but try this...
=SUMPRODUCT(--(--MID(A2:A6,4,5) < 5000))

oooooops apologies, I did not even see sheet 2

[Tony Valko]

Y'all are gonna miss me when I'm gone!

[FDibbins]

Try this ARRAY formula...
=IFERROR(INDEX(Sheet2!A:A,SMALL(IF(Sheet2!$B$2:$B$14< 5000,ROW(Sheet2!$A$2:$A$14)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

(I know Tony also posted this, but I worked it out so I am posting it lol)

[FDibbins]

Y'all are gonna miss me when I'm gone!

you singing a song, or...where you going???

[jcswaby]

Many thanks for that, it works great.

[Tony Valko]

Can't tell who you're replying to but we do appreciate the feedback!