Excel COUNTIF with Multiple Criteria Across Different Columns

When working with Excel datasets, you often need to count how many rows meet multiple conditions across different columns. While the COUNTIF function handles a single condition, the COUNTIFS function allows you to apply multiple criteria simultaneously, making your data analysis more precise and powerful.

In this article, you’ll learn practical ways to count rows based on multiple criteria in different columns using COUNTIFS function and related formulas. Each method includes a clear explanation and example to help you get accurate counts based on your data.

This method uses the COUNTIFS function to count rows where all specified conditions in different columns are met simultaneously. For example, you can count how many times “T-Shirt” products were sold in “Store A.”

To demonstrate, we will use a simple dataset containing four columns: Product, Color, Store, and Quantity. Each row lists a product entry with its corresponding attributes, which we’ll use to apply various COUNTIF and COUNTIFS formulas across different columns.

Steps:

➤ Select a blank cell (e.g., E2).
➤ Use the formula:

➤ Press Enter to see results.

This formula returns the total number of rows where both conditions match, giving you a precise count of “T-Shirt” sales in “Store A“.

This method evaluates multiple conditions at once using the COUNTIFS function. For example, we’ll count how many rows meet both of the following: the store is “Store A” and the quantity sold is greater than 40. If both conditions are met in a row, it’s included in the final count which gives you the number of high-quantity sales from that specific store.

Steps:

➤ Select a blank cell (e.g., E2).
➤ Enter the formula:

➤ Press Enter to see results.

This formula outputs the count of rows meeting both the store name and quantity threshold which helps identify higher sales in specific locations.

This method shows how to count rows that meet at least one of several conditions using COUNTIFS function with array constants. Since the COUNTIFS function handles only AND logic, we simulate OR logic by summing separate conditions.

For example, we’ll count how many times either a “T-Shirt” or a “Hoodie” was sold in “Store B“. This lets you combine similar product types under one condition and still apply filters across another column like Store.

Steps:

➤ Select a blank cell (e.g., E2).
➤ Enter the formula:

➤ Press Enter to see results.

This formula returns the combined count of “T-Shirt” and “Hoodie” sales in “Store B,” giving you the total matches for either product in that store.

This method counts how many times specific keywords appear anywhere within a multi-column range using the COUNTIF function and an array constant. For instance, we’ll check how often “Red,” “Yellow,” and “Store A” appear across columns A to D.

This is helpful when you’re scanning an entire table for repeated entries of key values, regardless of which column they appear in. The output will show the count of each keyword’s total occurrences in the dataset.

Steps:

➤ Select a blank cell (e.g., E2).
➤ Enter the formula:

➤ Press Enter to see results.

This formula returns the count of each keyword’s occurrences in the range showing how often each appears across all columns.

This method counts rows that match specific pairs of values across two columns using the COUNTIFS function with arrays. For example, you can check how many times “T-Shirt” appears in “Store A,” “Hoodie” in “Store B,” and “Pants” in “Store C.” The formula compares each pair of product and store across corresponding rows. You’ll get either individual match counts or a total sum, depending on whether or not you wrap it in a SUM function.

Steps:

➤ Select a blank cell (e.g., E2).
➤ Enter the formula:

➤ Press Enter to see results.

This returns an array with counts for each product-store pair.

➤ To get the total count across all pairs, wrap the formula with SUM function:

Now this sums all matching rows for the specified pairs in our selected cell.

Frequently Asked Questions

How do I count rows based on multiple criteria in different columns?

Use the COUNTIFS function by pairing each condition with its corresponding column range. This function checks all conditions row-by-row and returns the count of rows that meet every specified requirement.

Can I use COUNTIF or COUNTIFS to apply OR logic?

COUNTIFS function is built for AND logic. To mimic OR logic, combine multiple COUNTIFS function statements using SUM function, where each one handles a separate condition. This approach totals matches across multiple criteria sets.

Does COUNTIFS support numeric comparisons?

Yes, COUNTIFS function can compare numeric values using operators like >, <, >=, or <=. This allows you to apply thresholds or ranges to count rows based on numerical conditions alongside text filters.

How can I count multiple keywords across several columns?

Use the COUNTIF function with an array of keywords, such as {“Red”,”Store A”}, across a multi-column range. This will return how often each keyword appears, helping identify frequency across the entire dataset.

Wrapping Up

In this tutorial, we have learned how to use COUNTIF and COUNTIFS functions in Excel to count rows meeting multiple criteria across different columns. Whether you need to apply AND logic, simulate OR conditions, or count multiple keywords and pairs, these formulas allow efficient and accurate data analysis. Feel free to download the practice file and share your feedback.