Function Problems

How to Count Positive Orders for a Specific Channel in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to count the number of orders for a specific channel (e.g., "Chelsea Peers UK") where the order values are positive, and potentially count the unique Order IDs that meet these criteria.

Product: Excel
Device & OS: not provided
Scenario: Analyzing sales or order data to find the count of positive transactions for a specific sales channel.
Observed behavior: The user requires a formula combination to filter data by channel, check for values greater than zero, and optionally count unique records without returning errors.

Before you start

Ensure your Excel or WPS Office version supports Dynamic Array functions like FILTER and UNIQUE to use the advanced formulas. For older versions, you will need to rely on the COUNTIFS function.

Solution 1Recommended

Count Total Positive Orders Using FILTER and COUNT

Use this solution to count the total number of positive values in your dataset that match a specific channel criterion.

By combining the COUNT and FILTER functions, you can isolate data matching your channel name and check for positive values simultaneously.

Select a blank cell

Click on the cell where you want the final count to be displayed.

Enter the formula

Type the formula `=COUNT(FILTER(B:B,(A:A="Chelsea Peers Uk")*(B:B>=1)))` into the formula bar.

Execute the formula

Press Enter. Excel will filter the column for the specific channel and positive values, returning the total count of matches.

Formula Breakdown: The asterisk (*) acts as an AND operator in the FILTER array, ensuring both the channel name and positive value conditions are met.

Solution 2

Count Unique Positive Order IDs

Apply this solution when you have duplicate order entries and only want to count each unique positive order ID once.

Solution 3

Alternative: Use COUNTIFS for Older Excel Versions

Use this method if the FILTER function returns a #NAME? error, which indicates your software version does not support dynamic arrays.

Use WPS Spreadsheet to Analyze Data

Count Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array formulas like FILTER and UNIQUE, allowing you to seamlessly analyze complex datasets, count positive orders, and filter multi-condition criteria effortlessly.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your order records.
2. Select the target cell: Click an empty cell where you want to output the counted result.
3. Input the array formula: Type your preferred formula (e.g., `=COUNT(FILTER(...))`) into the formula bar.
4. Calculate the result: Press Enter to instantly process your data conditions and return the accurate count.

100% compatible with Microsoft Excel formats (.xlsx)Native support for advanced dynamic array functionsLightweight, fast, and highly efficient for large data sets

Frequently Asked Questions

Why does my FILTER formula return a #NAME? error?

A #NAME? error generally means that your version of Excel or spreadsheet software does not support the FILTER function. Dynamic array functions were introduced in newer versions (Microsoft 365, Excel 2021, and latest WPS Office). If you encounter this, use the COUNTIFS alternative instead.

Can I count positive values using multiple channels?

Yes. If you need to count orders for multiple channels simultaneously, you can use the addition (+) operator in the FILTER array to represent an OR condition. For example: `(A:A="Channel 1")+(A:A="Channel 2")`.

How does Excel handle blank cells in the criteria column?

When using `B:B>0`, blank cells are automatically evaluated as 0, meaning they will not be counted as positive values. However, it is always best practice to reference the exact data range (like `B2:B100`) rather than the entire column (`B:B`) to improve formula performance.

Excel & Data Problems