Pivot Table Issues

How to Summarize Excel Data with Multiple Criteria Using Formulas or Pivot Tables

Steve K Oct 10, 2026 869 views

Question details

Create an organization-based summary tracking outstanding, completed, blank, and N/A items from a dynamic dataset while maintaining access to underlying records.

How to Create a Data Summary with Multiple Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing and summarizing a frequently changing dataset that involves multiple distinct status criteria (dates, blanks, N/As) grouped by organization.
Observed behavior: The user requires a scalable method to accurately tally multiple item statuses per organization and efficiently drill down into the specific details.

Before you start

Ensure your dataset is formatted as a standard table with clear, unique column headers and no merged cells to guarantee that formulas and Pivot Tables calculate accurately.

Solution 1Recommended

Use Dynamic Formulas (UNIQUE and COUNTIFS)

A formula-based calculation table is often easier to customize for highly specific, multiple-criteria summaries than a standard Pivot Table.

When dealing with very specific edge cases—such as counting blank cells, N/A errors, or date ranges alongside standard statuses—combining Excel's dynamic array functions with conditional counting is highly effective.

Extract unique organizations

In a new summary sheet, type =UNIQUE(B:B) (assuming column B contains your organizations) to automatically generate a deduplicated list.

Count specific statuses

In the adjacent column, use a formula like =COUNTIFS(Data!B:B, A2, Data!C:C, "Outstanding") to count items that meet specific criteria for that organization.

Count blank cells and date ranges

To count missing data, use =COUNTIFS(Data!B:B, A2, Data!D:D, ""). For date ranges, use logical operators such as "<"&TODAY().

Expand the formulas

Copy your COUNTIFS formulas down the column so they automatically apply to any new organizations added by the UNIQUE function.

Use Dynamic Formulas (UNIQUE and COUNTIFS)

Dynamic Updates: Formula-based summaries update instantly when the underlying data changes, without needing the manual refresh required by Pivot Tables.

Solution 2

Create a Pivot Table with Drill-Down

Use a Pivot Table to quickly group organizations and use the double-click drill-down feature to view underlying records.

Efficient Data Summarization in WPS Office

Create Complex Summaries and Pivot Tables Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful data analysis tools, including advanced COUNTIFS formulas, dynamic arrays, and robust Pivot Tables. Fully compatible with Microsoft Excel formats, it makes managing and summarizing frequently changing datasets simple.

1. Open your dataset: Launch WPS Spreadsheet and open your dataset containing the organizational records.
2. Insert a PivotTable: Go to the Insert tab on the top ribbon and select PivotTable to open the creation wizard.
3. Configure fields: Drag the Organization field into the Rows area, and drop your status or date fields into the Values area.
4. Apply dynamic formulas: For highly custom tracking, use the formula bar to enter UNIQUE and COUNTIFS functions to tally blanks, N/As, and completed tasks.

Seamlessly opens, edits, and saves Microsoft Excel (.xlsx) files.Built-in powerful Pivot Table engine with double-click drill-down functionality.Supports advanced formulas like UNIQUE and COUNTIFS for complex multi-criteria tracking.Lightweight, fast, and completely free to use with a familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I view the underlying data of a specific Pivot Table count?

Simply double-click the value cell in your Pivot Table. Both Excel and WPS Spreadsheet will automatically extract the data and generate a new sheet displaying all the source records that make up that specific number.

Can COUNTIFS handle dates before or after a specific day?

Yes. You can use logical operators enclosed in quotes, such as ">="&DATE(2023,1,1) or "<"&TODAY(), as the criteria argument in your COUNTIFS formula.

Why is my Pivot Table not showing the newest data?

Pivot Tables do not update automatically when source data changes. You need to right-click anywhere inside the Pivot Table and select 'Refresh', or go to the PivotTable Analyze tab and click 'Refresh All'.

How do I count blank cells using COUNTIFS?

To count blank cells that meet other conditions, use "" (double quotes with nothing inside) as the criteria argument for the range you are checking for blanks.

Excel & Data Problems