Function Problems

How to Count Rows with Y in Either Column Using Excel COUNTIF

Emma Brown Oct 9, 2026 869 views

Question details

The user needs a formula to count rows where at least one of two specific columns contains the value 'Y', ensuring rows with 'Y' in both columns are only counted once.

How to Count Rows with 'Y' in Either Column Using Excel COUNTIF

Product: Excel
Device & OS: not provided
Scenario: Data analysis requiring conditional counting across multiple columns using an OR logic without duplicating counts for rows meeting both conditions.
Observed behavior: The user needs to evaluate two columns per row and return a total count of rows meeting the criteria without double-counting.

Before you start

Identify the exact columns you want to evaluate in your spreadsheet (e.g., columns AK and AL) and select an empty column to serve as your temporary helper column.

Solution 1Recommended

Use a Helper Column with IF and OR Functions

The most reliable and easy-to-understand method to prevent double-counting is to use a helper column to evaluate each row individually with an IF and OR formula, then count those results.

Directly using COUNTIF with an OR condition across multiple columns can be complex or lead to double counting if both columns meet the criteria. A helper column simplifies the logic by identifying the matching rows before generating the final count.

Create a Helper Column

Click on the first cell of a blank column next to your data (for example, cell AM4).

Enter the IF/OR Formula

Type `=IF(OR(AK4="Y",AL4="Y"),"Y","")` and press Enter. This formula checks if either AK4 or AL4 contains 'Y'. If true, it returns 'Y'; otherwise, it leaves the cell visually blank.

Apply Formula to All Rows

Select cell AM4, click and hold the small square at the bottom-right corner of the cell (the Fill Handle), and drag it down to fill the formula through the rest of your data rows (e.g., down to AM60).

Count the Helper Column Results

Click the cell where you want your final total count to appear. Enter the formula `=COUNTIF(Sheet1!$AM$4:$AM$60,"Y")` and press Enter. This gives you the total count without any duplicates.

Use a Helper Column with IF and OR Functions

Alternative to Blank Cells: You can replace the empty quotes `""` in the formula with `"N"` (i.e., `=IF(OR(AK4="Y",AL4="Y"),"Y","N")`) if you prefer the helper column to display an 'N' when the condition is not met.

Solution 2

Use SUMPRODUCT to Count Without a Helper Column

If you prefer not to add extra columns to your spreadsheet, you can use the SUMPRODUCT function to evaluate the OR logic in a single advanced array formula.

Efficient Data Analysis with WPS Office

Easily Manage Complex Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports all advanced Excel functions, including COUNTIF, IF, OR, and SUMPRODUCT. It offers a smooth, feature-rich experience for your data analysis workflows.

1. Open your file: Launch WPS Spreadsheet and open your existing workbook containing the dataset.
2. Insert the Helper Column: Add a new column next to your data and use the intuitive formula bar to type `=IF(OR(AK4="Y",AL4="Y"),"Y","")`.
3. Apply COUNTIF: Use the built-in Insert Function tool to effortlessly set up your `=COUNTIF()` formula and instantly get your accurate row count.

100% compatible with Microsoft Excel formulas and .xlsx files.Intuitive formula builder to help you quickly write nested IF and COUNTIF statements.Free, lightweight, and fast performance even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just add two COUNTIF formulas together?

Using a formula like `=COUNTIF(AK:AK,"Y") + COUNTIF(AL:AL,"Y")` will count 'Y' in column AK and 'Y' in column AL independently. If a single row has 'Y' in both columns, it will be counted twice, resulting in an inaccurate total row count.

Can I use COUNTIFS to solve this instead?

The standard COUNTIFS function uses AND logic, meaning it only counts rows where ALL criteria are met (e.g., 'Y' in AK AND 'Y' in AL). It does not natively support OR logic across different columns without complex array manipulation.

How do I make the formula case-sensitive?

Standard functions like COUNTIF and IF(OR(...)) are not case-sensitive. To make it case-sensitive (e.g., only counting an uppercase 'Y'), you would need to use the EXACT function inside your helper column, such as `=IF(OR(EXACT(AK4,"Y"),EXACT(AL4,"Y")),"Y","")`.

Excel & Data Problems