Function Problems

How to Count Rows Based on Two Column Criteria in Excel

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user wants to count the number of rows that meet two distinct conditions across two columns and calculate the percentage of those matching rows.

How to Count Rows Based on Two Column Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing a dataset where records must match specific text values in two different columns (e.g., filtering by jersey color and pants color simultaneously) to evaluate occurrences and calculate proportions.
Observed behavior: The user needs the correct formula combination to count multi-criteria rows and accurately calculate their percentage, as attempting to reference PivotTable data directly resulted in an incorrect zero value.

Before you start

Ensure that your target columns have consistent data without hidden trailing spaces, and that the ranges you plan to compare in your formula are of the exact same size.

Solution 1Recommended

Use the COUNTIFS Function for Multiple Criteria

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

Unlike COUNTIF, which only evaluates one condition, COUNTIFS allows you to apply multiple criteria to different ranges simultaneously. This is ideal for intersecting data points in different columns.

Select the target cell

Click on an empty cell where you want the final count to appear.

Enter the COUNTIFS formula

Type the formula =COUNTIFS(B:B, "Green", C:C, "Black"). Replace B:B and C:C with your actual column letters, and replace "Green" and "Black" with your specific lookup text.

Calculate the result

Press Enter to execute the formula and view the total number of rows matching both conditions.

Use the COUNTIFS Function for Multiple Criteria

Range Sizing Rule: Always ensure the criteria ranges in COUNTIFS are the exact same size (e.g., B2:B100 and C2:C100). If the ranges mismatch, Excel will return a #VALUE! error.

Solution 2

Calculate the Percentage of Matching Rows

Combine COUNTIFS with COUNTIF to determine what percentage of a specific category meets your second criteria.

Solution 3

Use a PivotTable for Multi-Criteria Analysis

PivotTables can automatically group and count occurrences based on multiple columns without writing complex formulas.

Data Analysis Made Easy

Easily Calculate Multiple Criteria Counts with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like COUNTIFS and COUNTIF, as well as robust PivotTable features. It provides a familiar interface to analyze complex datasets seamlessly and accurately.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your dataset.
2. Apply the Formula: Select a blank cell and input your COUNTIFS formula to calculate multiple criteria exactly as you would in Microsoft Excel.
3. Format Your Results: Use the Home tab to quickly format your resulting data into percentages or add custom visual styles.

Fully compatible with Microsoft Excel formulas like COUNTIFS, SUMIFS, and VLOOKUPIntuitive PivotTable interface for quick data summarization and percentage calculationFree, lightweight, and fast alternative for professional data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return a #VALUE! error?

This error generally occurs when the criteria ranges provided in the formula are not the exact same size. For instance, pairing range B1:B100 with C1:C99 will break the formula. Ensure both ranges have matching start and end rows.

Can I use cell references instead of hardcoded text in COUNTIFS?

Yes, you can substitute text criteria with cell references. For example, instead of using "Green", you can write =COUNTIFS(B:B, F1, C:C, G1) where cells F1 and G1 contain the text you want to evaluate.

How do I turn off GETPIVOTDATA when clicking on a PivotTable?

To stop Excel from automatically wrapping your cell clicks in a GETPIVOTDATA function, go to the PivotTable Analyze tab, click the drop-down arrow next to the 'Options' button, and uncheck 'Generate GetPivotData'.

Does the COUNTIFS function support wildcard characters?

Yes, you can use wildcards in your text criteria. Use an asterisk (*) to match a sequence of characters (e.g., "*shirt" matches "red shirt" and "blue shirt") or a question mark (?) to match a single character.

Excel & Data Problems