top of page

Use Case: Client-Wise Order Status Summary in Excel

Background

A business needed to quickly summarize how many orders were Cancelled, Pending, and Completed for each Client in an Excel workbook. Manually filtering and counting was tedious and error-prone. We designed a simple, powerful formula to automate this task dynamically.

Problem

In the given Excel file:

  • Sheet1 had columns:

    • Order ID (Column A)

    • Client ID (Column B)

    • Order Status (Column G)

  • Sheet2 had only Client IDs listed (Column A).

  • The goal was:
    For each Client ID in Sheet2, find and count the number of Cancelled, Pending, and Completed orders from Sheet1, and show the result in a single cell in Sheet2 Column B.

Challenges

  • Matching Client IDs across two sheets.

  • Summarizing multiple Order Status categories (Cancelled, Pending, Completed).

  • Displaying counts together in a single readable format.

  • Keeping the solution dynamic and automatic as data updates.

Our Solution

We created a single smart formula using TEXTJOIN, COUNTIFS, and FILTER functions.

👉 Here is the formula to paste in Sheet2!B2:

​

=TEXTJOIN(", ", TRUE, IFERROR(FILTER({"Cancelled", "Pending", "Completed"}, (COUNTIFS(Sheet1!B:B, A2, Sheet1!G:G, {"Cancelled", "Pending", "Completed"})>0)), "") & ": " & FILTER(COUNTIFS(Sheet1!B:B, A2, Sheet1!G:G, {"Cancelled", "Pending", "Completed"}), COUNTIFS(Sheet1!B:B, A2, Sheet1!G:G, {"Cancelled", "Pending", "Completed"})>0))

How It Works:

  1. COUNTIFS counts how many orders exist for each status (Cancelled, Pending, Completed) for the Client ID in A2.

  2. FILTER selects only statuses where count is greater than 0 (to avoid showing 0s).

  3. TEXTJOIN neatly joins the status names and counts with commas.

Example Output

For a client with:

  • 2 Cancelled orders

  • 3 Pending orders

  • 1 Completed order

The result in Sheet2!B2 will display:
Cancelled: 2, Pending: 3, Completed: 1

Benefits of This Approach

  • Fully automatic – no manual counting required.

  • Single formula handles all statuses dynamically.

  • Easy to expand if more statuses are added in future (just adjust the FILTER list).

  • Saves hours of manual effort in reporting.

bottom of page