Function Problems

How to Count Values in One Column Based on Another Column in WPS Spreadsheet

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to count specific values in one column (such as 'Yes' or 'Paid') only when a corresponding value in another column meets a designated condition (such as a specific number or chapter name).

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: Data analysis requiring conditional counting of items across multiple columns with specific paired criteria.
Observed behavior: The user wants a formula to count entries in a target column based on matching criteria in a reference column.

Before you start

Ensure your data is organized into clear columns without merged cells, and verify that the two columns you are comparing contain the exact same number of rows.

Solution 1Recommended

Use the COUNTIFS Function for Multiple Criteria

The COUNTIFS function allows you to count the number of cells that meet multiple criteria across different ranges, making it perfect for counting based on another column's value.

The syntax for this function is =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2). It checks if conditions are met simultaneously in the specified rows.

Select the target cell

Click on the empty cell where you want the final counted result to be displayed.

Enter the formula for numerical and text criteria

To count 'Yes' in column B when the corresponding value in column A is 2, type the formula: =COUNTIFS(A:A, 2, B:B, "Yes").

Enter the formula for multiple text criteria

To count 'Paid' entries in column P based on a specific chapter name in column N, type: =COUNTIFS(N:N, "Chapter Name", P:P, "Paid").

Use cell references instead of text

Instead of typing the text directly into the formula, you can refer to a cell containing the text criteria. For example, if cell E1 contains the chapter name, modify the formula to: =COUNTIFS(N:N, E1, P:P, "Paid") and press Enter.

Formatting Text Criteria: When using text strings as criteria in formulas, always enclose them in double quotation marks (e.g., "Yes"). Numbers and cell references do not require quotation marks.

Advanced Formula Support

Easily Count and Analyze Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis functions like COUNTIFS to help you quickly process complex data. It is fully equipped to handle advanced mathematical and conditional queries.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing the columns you wish to analyze.
2. Start your formula: Click on a blank cell, type '=' and begin typing 'COUNTIFS'.
3. Follow the formula prompt: Use the on-screen formula guide to select your first criteria range and input the condition, followed by the second range and its condition.
4. Get instant results: Press Enter to instantly view your calculated count.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Lightweight and fast, running smoothly on all devicesBuilt-in formula hints and error-checking tools for better accuracyFree to download and use for personal, everyday data tasks

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF is used when you only have one condition to check in a single range. COUNTIFS is used when you need to evaluate multiple conditions across one or more ranges simultaneously.

Why is my COUNTIFS formula returning an error or 0?

This usually happens if the criteria text does not exactly match the cell contents (like hidden trailing spaces), or if the criteria ranges are not identical in size. Ensure both ranges (e.g., A:A and B:B) span the same rows.

Can I use wildcards with the COUNTIFS function?

Yes, you can use wildcards like the asterisk (*) to match any sequence of characters or the question mark (?) to match a single character within your text criteria (e.g., "*Paid*").

Excel & Data Problems