How to Use Excel COUNTIFS Formula for a Summary Table
Question details
The user needs to create a summary table that counts records matching both a specific column heading and a row criterion using the COUNTIFS function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a cross-tabulated summary matrix where each cell must calculate counts based on two dynamic criteria: the row header and the column header.
- Observed behavior
- Requires a formula setup utilizing absolute references for data ranges and mixed references for criteria so the formula can be dragged across and down without breaking.
Ensure your source data ranges do not contain leading or trailing spaces, as COUNTIFS requires an exact character match between the criteria in your summary table and the source data.
Use COUNTIFS with Mixed and Absolute References
Apply a COUNTIFS formula combining absolute references for data ranges and mixed references for row and column headers to quickly fill a complete summary matrix.
The COUNTIFS function counts the number of cells that meet multiple criteria. By locking the source data ranges with absolute references (like $D$7:$D$29) and using mixed references for your summary table headers (like H$5 and $G6), you only need to write the formula once. You can then drag it to automatically calculate the intersecting counts for the entire table.
Click on the top-left blank cell of your summary table where the first row and column criteria intersect.
Type the formula matching your data layout. For example: =COUNTIFS($D$7:$D$29, H$5, $E$7:$E$29, $G6). Here, $D$7:$D$29 and $E$7:$E$29 are the locked source ranges. H$5 references the column header (row locked), and $G6 references the row header (column locked).
Press Enter to confirm the formula. Click the fill handle (the small square at the bottom-right corner of the cell) and drag it across the columns, then drag it down the rows to populate the rest of the summary table.
Create Summary Tables Easily with WPS Spreadsheet
WPS Spreadsheet fully supports COUNTIFS and other advanced data analysis formulas. Use it to effortlessly build summary tables, analyze complex datasets, and apply mixed references with the exact same syntax as Microsoft Excel.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your source data and summary table layout.
- 2. Input the COUNTIFS function: Select the first cell of your summary table and enter your COUNTIFS formula, using F4 to easily toggle absolute and mixed references.
- 3. Auto-fill the remaining cells: Drag the fill handle across the columns and down the rows to instantly complete your summary matrix.
Frequently Asked Questions
Why is my COUNTIFS formula returning 0 instead of the actual count?
This usually happens when the criteria in your summary headers do not exactly match the source data. Check for hidden trailing spaces, typos, or different formatting (e.g., 'Status 1' vs 'Status1').
What is the difference between absolute and mixed references in this formula?
Absolute references (e.g., $A$2:$A$10) lock both the column and row so the data range doesn't shift when copying. Mixed references (e.g., H$5 or $G6) lock either the row or the column, allowing the formula to adapt dynamically to the summary table's respective headers.
Can I use COUNTIFS with partial text matches?
Yes, you can use wildcard characters like an asterisk (*) or question mark (?) in your criteria. For example, using '*apple*' as a criterion will count any cell containing the word 'apple' within the text string.
Does WPS Office support the COUNTIFS function?
Yes, WPS Spreadsheet fully supports the COUNTIFS function, utilizing the exact same syntax, wildcard rules, and referencing logic as Microsoft Excel.
