Function Problems

How to Count Excel Rows That Meet Two Conditions Using COUNTIFS

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to count the number of rows in a spreadsheet that simultaneously meet at least two specific conditions.

How to Count Excel Rows That Meet Two Conditions Using COUNTIFS

Product: Excel
Device & OS: not provided
Scenario: Analyzing tabular data to calculate the total count of records that match multiple criteria, such as applicants who are 18 years old and whose decision is marked as Approved.
Observed behavior: The user is looking for the correct formula structure to count rows satisfying multi-condition logic.

Before you start

Ensure your dataset is organized in clear columns without merged cells, and identify the exact columns that contain the criteria you want to evaluate.

Solution 1Recommended

Use the COUNTIFS Function

The COUNTIFS function is the standard and most efficient method to count cells across multiple ranges that meet multiple criteria.

The COUNTIFS function works by checking multiple criteria ranges against specific conditions. All conditions must be met for a row to be counted.

Select the destination cell

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

Start the formula

Type `=COUNTIFS(` to initiate the function.

Enter the first criteria

Select the first range and type your first condition. For example, to check if column A contains 18, enter `A:A, 18`.

Enter the second criteria

Add a comma, then select the second range and condition. For example, to check if column C says Approved, enter `C:C, "Approved"`.

Complete the formula

Close the parenthesis and press Enter. Your final formula should look like this: `=COUNTIFS(A:A,18,C:C,"Approved")`.

Text Criteria: Always remember to enclose text strings (like "Approved") in double quotation marks within your formula.

Master Data Analysis

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports the COUNTIFS function and all standard Excel formulas, allowing you to seamlessly analyze complex datasets for free.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your dataset.
2. Access the Function Wizard: Select an empty cell, navigate to the 'Formulas' tab on the ribbon, and click 'Insert Function'.
3. Apply COUNTIFS: Search for 'COUNTIFS', select it, and follow the visual prompts to input your ranges and conditions without manually typing the syntax.

100% compatible with Microsoft Excel formulas and the .xlsx file format.Built-in intuitive Function Wizard to easily construct multi-criteria formulas.Free, lightweight, and available across Windows, Mac, iOS, and Android devices.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use COUNTIFS with more than two conditions?

Yes, the COUNTIFS function allows you to add up to 127 range and criteria pairs in a single formula. Just continue adding commas followed by the new range and condition.

Why is my COUNTIFS formula returning a 0?

This usually happens if no single row meets all the specified conditions simultaneously. It can also occur if text conditions aren't enclosed in quotation marks, or if there are hidden spaces in your data cells.

How do I use greater than or less than in a COUNTIFS formula?

To use logical operators, wrap them in double quotation marks along with the number, such as `">18"`. If you are referencing a cell value, use an ampersand to concatenate, like `">"&A1`.

Excel & Data Problems