ThisCalculator

Everyday

Count Columns with Specific Values in Excel

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to count columns that contain specific values in Excel, whether you need to count cells within a single column that match a condition, or count entire columns that have at least one match. You will learn the core functions—COUNTIF, COUNTIFS, SUMPRODUCT, and array formulas—and see practical examples for each.

Formula

The basic formula to count cells that contain a specific value is:

COUNTIF(range, criteria)

Where:

  • range – the group of cells you want to search (e.g., A1:A10).
  • criteria – the value you are looking for, which can be a number, text, or a wildcard expression (e.g., “Apple”, “>5”, “*apple*”).

For counting across multiple columns while checking each column as a whole, you would use a more advanced formula with SUMPRODUCT or an array formula, which we explain below.

Worked Example

Suppose you have the following data in column A (A1:A10):

  1. Apple
  2. Banana
  3. Apple
  4. Cherry
  5. Apple
  6. Date
  7. Apple
  8. Elderberry
  9. Fig
  10. Apple

You want to count how many cells contain “Apple”. Using the formula:

=COUNTIF(A1:A10, “Apple”)

Excel counts each cell that exactly matches “Apple” (case-insensitive). The result is 5.

Now, what if you want to count columns that contain at least one “Apple” across a range of columns (e.g., A:C)? You would use:

=SUMPRODUCT(–(COUNTIF(OFFSET(A1:C1, ROW(A1:C10)-ROW(A1), 0), “Apple”)>0))

This is an array formula that checks each row across the three columns and counts rows where at least one cell in that row contains “Apple”. For our example, if each row has one fruit and only column A has “Apple”, the result is 5 rows (not columns). To count columns, you would transpose the logic.

For counting columns (not rows), you can use:

=SUMPRODUCT(–(COUNTIF(INDEX(A:C,0,COLUMN(A:C)), “Apple”)>0))

This formula counts how many columns in the range A:C contain at least one “Apple”. In our example, only column A contains “Apple”, so the result is 1.

When to Use COUNTIF vs COUNTIFS

Use COUNTIF when you have a single condition. For example, count all cells equal to “Apple”.

Use COUNTIFS when you have multiple conditions across different columns. For instance, count rows where column A is “Apple” and column B is “Red”. The syntax is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

For counting columns that contain a specific value, you often need to combine functions like SUMPRODUCT with COUNTIF or use array formulas, because COUNTIF alone works on a single range.

How to Count Columns with Any Match (Advanced)

To count how many columns in a range contain at least one occurrence of a value, you need to evaluate each column separately. Here are two methods:

Method 1: SUMPRODUCT with COUNTIF

If your data is in columns A through C (rows 1 to 10), use:

=SUMPRODUCT(–(COUNTIF(INDEX(A:C,0,{1,2,3}), “Apple”)>0))

This works by creating an array of counts for each column (1, 2, 3) and then checking if each count is greater than zero. The double negative (–) converts TRUE/FALSE to 1/0, and SUMPRODUCT sums them.

Method 2: Array Formula (Ctrl+Shift+Enter in older Excel)

In Excel 365 or Excel 2021, you can use dynamic arrays. For older versions, you may need to press Ctrl+Shift+Enter.

=SUM(–(COUNTIF(INDIRECT(“A”&ROW(1:10)&”:C”&ROW(1:10)), “Apple”)>0))

This counts rows, not columns. To count columns, change the orientation.

For most users, the SUMPRODUCT method is simpler and does not require array entry.

Common Mistakes and How to Avoid Them

  • Mixing up rows and columns – When you want to count columns, ensure your formula is checking each column as a whole, not each row. The INDEX function with COLUMN() helps.
  • Using exact match when you need partial match – If you want to count cells that contain “apple” as part of a longer text (e.g., “pineapple”), use wildcards: “*apple*”.
  • Case sensitivity – COUNTIF is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
  • Blank cells – COUNTIF ignores blank cells, which is usually fine, but if you need to count blanks, use COUNTBLANK.

Alternatives to COUNTIF

If you are working with large datasets or need more flexibility, consider:

  • SUMPRODUCT – Can handle multiple conditions and array operations without Ctrl+Shift+Enter.
  • FILTER (Excel 365) – Dynamically returns all matching rows, and you can count them with COUNTA.
  • Power Query – For complex transformations, you can group and count columns.
  • PivotTables – Quickly count occurrences of values across columns by dragging fields.

Frequently Asked Questions

How do I count cells that contain a specific text in Excel?

Use =COUNTIF(range, “*text*”) to count cells that contain the text anywhere. For exact match, omit the asterisks.

Can COUNTIF count across multiple columns?

Yes, but not directly. You can use =SUMPRODUCT(COUNTIF(range1, criteria)+COUNTIF(range2, criteria)) to count across multiple columns, or use COUNTIFS for row-wise conditions.

How do I count columns that have at least one value in Excel?

Use =SUMPRODUCT(–(COUNTIF(INDEX(A:C,0,COLUMN(A:C)), “Value”)>0)) to count columns that contain the value at least once.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells that meet a single condition. COUNTIFS counts cells that meet multiple conditions across different ranges, all of which must be true.

Why is my COUNTIF formula returning 0?

Check that the criteria is spelled correctly, the range is correct, and that there are no extra spaces. Also, if you are using wildcards, ensure they are placed correctly.

How do I count cells that contain a number greater than a specific value?

Use =COUNTIF(range, “>10”) to count cells greater than 10. You can use comparison operators like >, <, >=, <=, and <>.

Related

Formula used
in d | Data range | - | A1:A10 | 0
in v | Value to count | - | Apple | 0
out count | Count of matching cells | - | COUNTIF(d, v) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.