Count Each Number in Excel Columns with COUNTIF or Power Query
Question details
You need to count how many times the numbers 0 through 9 occur in each of several Excel columns and present the totals in a summary table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- You have multiple columns containing numbers and want a reusable, non-PivotTable summary of digit frequencies.
- Observed behavior
- The goal state is a summary table that shows the count of each digit from 0 to 9 for every selected column.
Open your WPS Office workbook and identify the data columns you want to count. Keep an empty area available, such as E1:F11, for the 0-9 list and the COUNTIF results.
Count Each Number with a COUNTIF Formula Summary Table
Use this formula-based method when you need a portable summary that works in WPS Office and Microsoft Excel without add-ins.
This method lists digits 0-9 in a helper column and uses COUNTIF with a locked column reference so the same formula can be copied across multiple data columns. It is more portable than PivotTable or add-in-dependent solutions.
Select cell E2, type 0, then fill down to E11 so cells E2:E11 contain the numbers 0 through 9.
Select F2, type =COUNTIF(A$2:A$6,$E2), and press Enter to count how many times 0 appears in column A.
Drag the fill handle from F2 down to F11, then drag it across to the right for each additional column to count the remaining digits and columns.
Add headers such as Digit, Column A, and Column B in E1, F1, and G1 so the summary table is easy to read.
Count Each Number with Power Query and Load to Power Pivot
Use this method when you want a query-based transformation that can handle larger datasets or feed Power Pivot for further analysis.
Count Digit Frequencies in WPS Office Spreadsheets
Create a formula-based count summary in WPS Office Spreadsheets using COUNTIF. The result works across WPS Office and Microsoft Excel, so you can count each digit 0-9 across multiple columns and share the workbook as a familiar .xlsx file.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open a blank workbook or your existing .xlsx file that contains the columns to count.
- 2. Enter the digit list: In an empty column, enter 0 through 9 in cells such as E2:E11 and add a heading in E1.
- 3. Apply the COUNTIF formula: In F2, enter =COUNTIF(A$2:A$6,$E2) and press Enter, adjusting the range to match your data.
- 4. Copy the formula: Drag the fill handle down to F11 and across columns to populate the summary table.
- 5. Save and share: Save the workbook as .xlsx to keep the formulas compatible with Microsoft Excel and share it with your audience.
Frequently Asked Questions
Can I count each number in Excel without a PivotTable?
Yes. List 0-9 in E2:E11, then use =COUNTIF(A$2:A$6,$E2) in F2 and copy it down and across. This creates a plain formula summary table.
Does the COUNTIF formula work when my columns have different ranges?
Yes. Adjust A$2:A$6 to the actual range for each source column, or use full-column references such as A:A if your data range is large and consistent.
How does Power Query count digits across multiple columns?
In Power Query, import the range, unpivot the numeric columns, group by column and value, and add a count column. Load the result to a table or to Power Pivot.
Is WPS Office compatible with Excel COUNTIF formulas?
Yes. WPS Office Spreadsheets supports COUNTIF and saves .xlsx files, so the formula summary works in both WPS Office and Microsoft Excel.
Why is the formula summary more portable than Power Pivot?
It does not depend on Power Pivot or add-ins being installed on the viewer's machine, so the summary remains visible in any compatible spreadsheet application.
