Calculating and listing items over 20

[Joe Gieder]

I have a list containing over 1000 items, each of these
contain the fields p/n, description, qty, cost, delivery
and supplier. What I'm trying to do is get all items with
a delivery of over 20 and have Excel list in seperate
columns and rows the p/n, description, qty, cost,
delivery and supplier for all of these. Sometimes there
maybe duplications of a number (say 22) and I need to see
all of them. Can this be done?

an exampl would be:
p/n descr qty cost del sup
123 thing 12 1.00 20 xyz
234 whatever 1 2.00 22 abc
456 widget 10 1.50 22 def
789 jig 5 2.00 10 any

Thanks In Advance for your help and advice.
If I need to try to be more specific please let me know.

Joe

 

[Roger Govier]

Hi Joe

One way would be to use Autofilter.
Highlight your headings
Data=>Filter=>Autofilter
Select the drop down on Qty and choose Custom
Use the drop down to select Greater than on Equal and put 20 in the right
hand pane

If you need to see totals for the filtered rows, then use the Subtotal
function
=SUBTOTAL(9,D21000) would give th total Cost for the filtered rows
displayed

 

[Joe Gieder]

I tried auto filter but not the results I was expecting,
the following formula is what I have so far:

=IF(ISERR(SMALL(IF('Priced BOM'!$X$3:$X$733>19,ROW
('Priced BOM'!$X$3:$X$733)),ROW()-ROW($I$5)+1)),"",INDEX
(INDIRECT("'Priced BOM'!$I$1:$I$733"),SMALL(IF('Priced
BOM'!$X$3:$X$733>19,ROW('Priced BOM'!$X$3:$X$733)),ROW()-
ROW($I$5)+1))) - Array entered

The problem is that I also have many items that say "stk"
and it includes them also. Sometimes I even get 3, 4, 5,
6... I guess becaus Excel sees them as being a number
greater than 20 (like it shows in auto filter).

Joe

 

[Roger Govier]

Hi Joe

Must be something wrong with your data.
Excel does dot see 3,4,5 etc as being greater than 20.
In a mixed list with "stk" etc. in it, I can still filter values >=20