Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom autofilter
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom autofilter
Store your condition in a helper column.
-- Gary''s Student - gsnu200726 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom autofilter
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. mto wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom autofilter
Thank you Dave. Your detailed response was a big help. :)
"Dave Peterson" wrote: 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. mto wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Autofilter | Excel Discussion (Misc queries) | |||
custom autofilter in excel | Excel Worksheet Functions | |||
Custom AutoFilter | Excel Worksheet Functions | |||
custom autofilter dropdown | Setting up and Configuration of Excel | |||
custom autofilter does not work | Excel Discussion (Misc queries) |