Filter a WPS Spreadsheets PivotTable to Show Names Appearing More Than Three Times
Question details
The user needs to filter a WPS Spreadsheets PivotTable so that only cardholder names occurring more than three times in the source data are displayed, using a helper column formula.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a large list of cardholder names in column B across 7,857 rows and wants a PivotTable filtered to names appearing more than three times.
- Observed behavior
- The PivotTable currently includes all names; the user wants it to display only names that occur more than three times.
Confirm that the source data has a header row and that the cardholder name column is consistently filled. If needed, insert a blank helper column next to the data before creating the formula.
Add a COUNTIF Helper Column and Filter the PivotTable
Use this method when you want to keep the source data intact and apply a straightforward TRUE/FALSE filter in the PivotTable.
The COUNTIF function counts how many times each name appears in the source range. Comparing the result to 3 creates a TRUE/FALSE helper field that the PivotTable can use as a filter.
In WPS Spreadsheets, open the worksheet that contains the source data. Insert a blank column next to the cardholder name column, for example column C.
In the first data row of the helper column, type =COUNTIF(B:B,B2)>3 and press Enter. The formula returns TRUE when the name in B2 appears more than three times in column B.
Drag the fill handle down through all 7,857 data rows so each cardholder name gets a TRUE or FALSE result.
Click anywhere in the PivotTable, then in the PivotTable Fields pane drag the helper column field to the Filters area.
Use the filter dropdown added to the PivotTable, select TRUE, and click OK. The PivotTable now shows only names that appear more than three times.
Filter a PivotTable by Name Count in WPS Spreadsheets
Use WPS Spreadsheets to add a COUNTIF helper column and apply it as a PivotTable filter, so you can instantly isolate names that occur more than three times without changing the original records.
- 1. Open your workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel or WPS spreadsheet that contains the cardholder names and PivotTable.
- 2. Create the COUNTIF helper column: Insert a helper column next to the name column, enter =COUNTIF(B:B,B2)>3, and fill it down through the data.
- 3. Add the helper field to the PivotTable Filters area: Refresh the PivotTable if needed, then drag the new helper column into the Filters area of the PivotTable Fields pane.
- 4. Select TRUE to view matching names: Open the filter dropdown in the PivotTable, select TRUE, and confirm to display only names that appear more than three times.
Frequently Asked Questions
Can I use this method if the names are in a different column?
Yes. Replace B:B and B2 in the formula with the actual name column reference. For example, if names are in column D, use =COUNTIF(D:D,D2)>3.
What does the formula =COUNTIF(B:B,B2)>3 actually do?
It counts how many times the value in B2 appears in column B and returns TRUE when the count is greater than 3, or FALSE otherwise. The PivotTable filter then shows TRUE records.
Do I need to refresh the PivotTable after adding the helper column?
If the PivotTable was created before the helper column, click the PivotTable and choose Refresh or Update from the PivotTable Analyze or Options tab to make the new field available.
How can I show names that appear three times or more?
Change the comparison from >3 to >=3 in the helper column: =COUNTIF(B:B,B2)>=3. Then select TRUE in the PivotTable filter.
What should I do if the helper column shows FALSE for every row?
Check that the name column is in the referenced column and that the formula points to the first data row. If names contain leading or trailing spaces, clean the data with TRIM or adjust the COUNTIF criteria.
