custom autofilter

[Guest]

I need the quantity total of specific intelligent part numbers in a list.
example:

(part_no) (quantity)
CV5P1/NL/WIN/NC 1000
CV5P1/SV/WIN 500
ELFAER/NL/MVS 15
CV4/NL/AIX 500
ACI/NL/WIN 121

I'd like to use the custom autofilter to filter/extract on rows that contain
multiple "contains" options like the following to get the desired result so I
can use the =SUBTOTAL(109,ref1,ref2) function on the filtered total quantity,
but only two evaluations fields are available under the custom autofilter.

example criteria:
part_no contains "NL" AND
part_no does not contain "NC" AND
part_no does not contain "CV4*" AND
part_no does not contain "CV5*"
....

example result:
ELFAER/NL/MVS 15
ACI/NL/WIN 121
Filtered Total Quantity = 136

Is there a way to add more fields to the custom autofilter? Do I need to
use the advanced filter to get this info and if so, how should I specify the
criteria text to get the desired result?

 

[Guest]

Store your condition in a helper column.

 

[Dave Peterson]

I'd still use Autofilter, but I'd add another column to my range with a bunch of
formulas and then filter on that:

With your data in column A (A2:Axx), I'd put this in B2:

=and(countif(a1,"*NL*")>0,
countif(a1,"*NC*")=0,
countif(a1,"*CV4*")=0,
countif(a1,"*cv4"*)=0)

And drag down to Bxx.

if =countif() is 0, then it's not there. If it's >0 (actually 1), then it's
there.

 

[Guest]

Thank you Dave. Your detailed response was a big help.