Excel Pivot Table advanced filter (Contains multiple words and Multiple criteria in formula)
Advanced filter is also possible in Excel pivot tables, and the results can be copied into a new worksheet. Like normal tables, we can use a condition or multiple conditions in the condition range of advanced filter, and we can combine the multiple conditions in fields or a formula, if the condition is complex and the field is not easy to combine, we can use formula combination.
Chapters:
0:00 Excel Pivot Table advanced filter
0:52 Multiple criteria in formula
Description:
Excel Pivot Table advanced filter (Contains multiple words and Multiple criteria in formula)
Note: The cc should be changed to greater than sign, the ee should be changed to less than sign in the formula.
one. Excel Pivot Table advanced filter
1. Suppose we want to filter for clothes that meet two criteria, i.e., clothes with Prices greater than 10 and Sales greater than 600, and copy the results to a new worksheet (Sheet3). In Sheet7, enter "Sum of Price($)" in cell E3, enter "Sum of Sales(pieces)" in cell F3, enter cc10 in E4, and enter cc600 in F4; the condition is ready.
2. Click Sheet8 in the lower left corner of the Excel window to switch to the worksheet, select the "Data" tab, click "Advanced" in the "Sort & Filter" group, open the "Advanced Filter" window, position the cursor to the input box on the right side of the "List range", click Sheet7 to switch back to this window, select the range of A3:C12 Sheet7!$A$3:$C$12 is automatically populated into the input box to the right of the List range.
3. Position the cursor to the input box on the right side of the "Criteria range", then automatically switch back to the Sheet8 window, click Sheet7 to switch to the window again, select the E3:F4 conditional cells, then Sheet7!$E$3:$F$4 will be automatically filled into the input box on the right side of the "Criteria range".
4. Select "Copy to another location", the input box on the right of "Copy to" already has content, delete them, select Cell A1, click "OK", then all clothes with prices greater than 10 and sales greater than 600 will be copied to Sheet8.
Note: In the pivot table, the field names are all added with the name of the operation you are doing, such as "sum" in the operation, and when you prepare the filter conditions, you should also write them into the field name, otherwise an error will occur. For example, if the field name "Sum of Sales(pieces)" includes "Sum", when using it as a condition, "SSum of Sales(pieces)" should be used as the field name of the condition, and "Sum" cannot be omitted.
Two. Multiple criteria in formula
1.Suppose we want to filter clothes with "t-shirt" in the "Product Name"and "Sales" greater than or equal, and copy to 600 to a new worksheet (Sheet8). Enter the formula =AND(FIND("t-shirt",A4:A12),"Sum of Sales(pieces)"cc=600) in cell E6 of Sheet7 worksheet.
2. Press "Enter" on the keyboard to return to #VALUE! Wrong, leave it alone, switch to the Sheet8, select the "Data" tab, click "Advanced", open the "Advanced Filteri" window, click the input box to the right of the "List range" to position the cursor there, switch back to the Sheet7, select A3:C12, then the selected range Sheet7!$A$3:$C$12 is automatically populated to the right of the List range.
3. Position the cursor to the input box on the right side of the "Criteria range", Excel will automatically switch back to the Sheet8 worksheet, if there are contents in the input box on the right side of the "Criteria range", clear them, switch to the Sheet7 worksheet, and select the two cells E5:E6, then select the range Sheet7!$E$5:$E$6 to be automatically filled as the "Criteria range".
4. Click "Copy to another location" to select it, then Excel will automatically switch back to Sheet8 again Worksheet Click the A1 cell, then Sheet8!$A$1 will be automatically filled into the input box on the right side of "Copy to".
5. Click OK, then the records that meet the conditions are filtered to the Sheet8 worksheet, and there is only one record.
Note: When selecting a formula, you need to select an empty cell above the formula, otherwise an error will occur.
6. Formula explanation =AND(FIND("t-shirt",A4:A12),"Sum of Sales(pieces)"cc=600)
The formula consists of two conditions, which are combined with an AND function and separated by a comma.
The first condition uses the Find function to find if the names of the clothes contain the "t-shirt", and if it does, returns a number greater than 0, otherwise it returns 0.
Note: The name of the field "Sum of Sales(pieces)" is quoted in the second condition ""Sum of Sales(pieces)" cc=600".
If both conditions return true (a number greater than 0), the AND function returns true.
Watch video Excel Pivot Table advanced filter (Contains multiple words and Multiple criteria in formula) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 31 March 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 367 once and liked it 4 people.