Function Problems

How to Create an Excel Summary with COUNTIFS or a PivotTable

Partner Editor Oct 9, 2026 868 views

Question details

The user needs to summarize customer survey responses by event and calculate how many customers answered 'Yes' to each question.

How to Summarize Customer Responses Using COUNTIFS or a PivotTable in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing survey or event feedback data by compiling specific textual responses into a numerical summary table.
Observed behavior: The goal is to automatically calculate the total count of a specific criteria (e.g., 'Yes') grouped by events and distinct questions.

Before you start

Ensure your source dataset is formatted as a continuous table with clear column headers (such as 'Event', 'Question', and 'Response') and contains no merged cells or blank rows.

Solution 1Recommended

Use a PivotTable to Summarize Responses Automatically

A PivotTable is the fastest and most efficient way to group events and count specific text responses without writing complex formulas.

PivotTables are highly recommended for large datasets because they allow you to drag and drop fields to instantly pivot your view and calculate totals.

Select the Source Data

Highlight your entire dataset, including the column headers. Navigate to the 'Insert' tab on the top ribbon and click on 'PivotTable'.

Create the PivotTable

In the pop-up dialog, choose to place the PivotTable on a 'New Worksheet' and click 'OK'.

Configure Rows and Columns

In the PivotTable Fields pane, drag the 'Event' field into the 'Rows' area to list your events. Drag the 'Question' field into the 'Columns' area.

Count the Responses

Drag the 'Response' field (the column containing 'Yes') into the 'Values' area. Ensure the value field setting is set to 'Count'.

Filter for Target Answers

If you only want to see 'Yes' answers, drag the 'Response' field into the 'Filters' area as well, and use the filter dropdown at the top of the PivotTable to select 'Yes'.

Use a PivotTable to Summarize Responses Automatically

Quick Updates: When new customer responses are added to your original data sheet, simply right-click anywhere on the PivotTable and select 'Refresh' to instantly update your counts.

Solution 2

Use the COUNTIFS Formula for a Custom Summary Table

The COUNTIFS function is ideal if you want to build a highly customized, permanently formatted summary table that updates in real-time as data changes.

Smart Data Summarization

Master PivotTables and Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust data analysis tools, allowing you to seamlessly create advanced PivotTables or use multi-criteria formulas like COUNTIFS to summarize complex survey results.

1. Open Your Dataset: Launch WPS Spreadsheet and open the file containing your customer response data.
2. Insert a PivotTable: Highlight your data, navigate to the Insert tab, and select PivotTable to generate your summary framework.
3. Apply COUNTIFS: Alternatively, manually construct your summary table and rely on WPS Spreadsheet's intelligent formula autocomplete to easily write your COUNTIFS functions.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive drag-and-drop interface for building PivotTables quickly.Comprehensive built-in formula library supporting standard Excel functions like COUNTIFS.Lightweight performance ensuring smooth operation even with massive datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning zero or a #VALUE! error?

This commonly occurs if the criteria ranges are not exactly the same size. For instance, if you use B2:B100 for the first range and C2:C150 for the second, COUNTIFS will return an error. Also, ensure there are no trailing spaces in your source text (e.g., 'YES ' instead of 'YES').

How do I count both 'Yes' and 'Maybe' responses in the same summary cell?

You can add two COUNTIFS formulas together. For example: =COUNTIFS(Range1, Criteria1) + COUNTIFS(Range1, "Maybe"). Alternatively, advanced users can use an array constant with the SUM function.

Can I use wildcard characters with COUNTIFS?

Yes, you can use wildcards. An asterisk (*) matches any sequence of characters, and a question mark (?) matches any single character. For example, using "*YES*" as your criteria will count 'Yes', 'Yes please', or 'Oh yes'.

What should I do if adding new data rows doesn't update my PivotTable?

If you added rows below the original data range you selected, the PivotTable won't see them. To fix this, go to the PivotTable Analyze tab and select 'Change Data Source' to expand the range. To prevent this in the future, format your source data as an Official Table before creating the PivotTable.

Excel & Data Problems